Change Price/Quantity

Hi Laura, I think you mean that the Changed_From and Changed_To values are string values? An int aka an integer value is a number and could be evaluated with the > or < operators, so I assume the problem youre having is trying to evaluate a string value with >/<. You can use the STR_TO_INT() formula to convert a string value to an integer, then use the >/< operators. -Tyler

Hi Tyler, I tried with the following formula, but it return '"-" for all result: CASE WHEN STR_TO_INT("_CEL_P2P_ACTIVITIES".CHANGED_TO) > STR_TO_INT("_CEL_P2P_ACTIVITIES".CHANGED_FROM) THEN INF. WHEN STR_TO_INT("_CEL_P2P_ACTIVITIES".CHANGED_TO) < STR_TO_INT("_CEL_P2P_ACTIVITIES".CHANGED_FROM) THEN SUP. ELSE ERRORE END

MN_Process_Mining: lue is a number and could be evaluated with the > or < operators, so I assume the proble Hi Laura, We are also facing similar issue with change log tables. We want to calculate difference in Old and New values that represents Date ( i.e. 20200214) but the type of this column is String. Is there any way we can convert these values from String to Date and calculate difference in days? Thanks in advance Amogh

Hi Amogh, you can use TO_TIMESTAMP for this: TO_TIMESTAMP("Table"."StringColumn", 'YYYYMMDD' ) This translates a string into a date. The second argument specifies the pattern that should be used to match your string. Best David

Hi David, Thanks for the hint, I tried TO_TIMESTAMP however, I am getting out of range error. This table is basically a change log (CDPOS) for all columns so there are values in all format (VARCHAR, INT, DATE etc.) for different columns. For column that I am interested in, has values like 00000000 or 20191231 so I am trying this with already filter to the column I am interested in but may be it is giving out of range error as there are other type of values for other columns? Can you suggest something in this scenario? Regards, Amogh

Hi, which Celonis version are you using (IBC, CPM4.5, ). Cheers David

We are using Celonis 4.5 on premise version

Hi, the problem is most likely that we only support dates after year 1400, 00000000 would be parsed as year 0000, which is below that. Maybe you could check for those values first: TO_TIMESTAMP(CASE WHEN STR_TO_INT(Table.Column) > 14000000 THEN Table.Column ELSE NULL END, YYYYMMDD) In the IBC and from CPM4.6, this has changed; there you get a NULL value (and a warning) if a string cannot be parsed to a timestamp correctly. Best David

Hi David, I tried suggested input but still getting error Operator STR_TO_INT is not compatible with an input of type DATE image946651 16.9 KB Do you think this can be handled with any simple workaround or need to wait for 4.6 upgrade? Thanks for your quick help Amogh

So Changes.Chngtime is already a date column? Then you dont need to do anything since you already have the date data type. I thought you have a string column and want to transform it into a date?

Hi David, Sorry, my bad I attached the wrong screenshot with wrong column. I now tried this with the VALUE_NEW column but was getting out of range error so I even tried using SUBSTRING function to filter values that are false positives for Date but still getting out of range error. I even checked all the values in this column but none of them appears to be out of range. image868617 18.2 KB

Hi, maybe there is a month 0 (like in 00000000), which is not allowed? Also, comparing strings with the less-than is risky. To check the month, I would suggest the following: STR_TO_INT(SUBSTRING("Changes"."value_new", 4, 2)) BETWEEN 1 AND 12 In CPM4.5, TO_TIMESTAMP is, as I said, unfortunately quite restrictive. So depending on your input values it might not be possible to exclude all wrong values by writing such conditions. It might be better to create a DATE column already on the Source Database side. Otherwise, you can wait for CPM4.6. Best David