Hi everyone,
My client would like to know if the change price or quantity is less or greater than the previous value indicated in the order.
The issue is that Change_from and Change_to in the Activities table is an int value and not a number.
What formula can I use knowing that > and < can not work?
Thank you
Laura
Page 1 / 1
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
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
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:
This translates a string into a date. The second argument specifies the pattern that should be used to match your string.
Best
David
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
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
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
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
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
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:
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
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 12In 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
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.