Filtering on substring of case name

Hi all,

I’m working with a dataset that apparently contains some testdata. However, the case names of these test cases are not named consistently, e.g., some are named ‘test 123’, ‘TEST BV’, ‘test upgrade’, ‘20192406 test’, etc.
Is it possbile in some way to filter these cases out within Celonis? So, for example by applying a CASE WHEN statement checking whether or not a specific substring (‘test’ for example) in in the case name? Of course, we could check this if the naming is consitent by just using the SUBSTRING command, but this only works if you specify the starting point of the substring which we don’t know.

Hope that someone can help.
Thanks in advance,

Best,
Menno

Hi Menno,

you can search a substring in a string using the PATINDEX function. It will return the start index of the given pattern within the input string if the pattern was found (integer >=1), and it will return 0 if the pattern was not found. So you can use PATINDEX( LOWER("Table"."Column"), '%test%' ) and check the result of that function in a FILTER or a CASE WHEN statement. I used LOWER in the proposed solution here to make it case insensitive.

Cheers
David

Hi David,

I am trying to use PATINDEX function in the IBC, However I get the error “Invalid Operator Type PATINDEX”. The data type of the input field is string . Could you please let me know if you what could be the issue.?

Thanks,
Soumya

Even i am looking for the same issue, Can anyone help please.

PATINDEX is currently only available in the Full IBC. If you are using CPM4.6 or below, or a Hybrid/LYDIP installation, the function is not available yet. In this case, you need to wait until the next release of your product.

Best
David

Could you filter these test cases out during the transformation rather than after the data is loaded into the Celonis data model? I would use a where clause like this in your case table transformation:

WHERE LOWER(CASE_TABLE.CASE_ID) NOT LIKE ‘%test%’