Change Price/Quantity

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

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 you’re 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

1 Like

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

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”

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 don’t 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.

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