Here's how to use the LIKE and INLIKE operators in PQL (Process Query Language) to perform string matching and comparisons in Celonis.
I provide an example using call center data to count the number of calls about credit cards. By using LIKE and wildcards, it matches activities with "credit card" in the text. I explain how to count distinct cases instead of overcounting activities. I also show how to combine multiple criteria, looking for both "credit card" and "survey offered." Important details like case sensitivity and using quotes are explained.
This short video demonstrates how to leverage LIKE and INLIKE to efficiently query and analyze text data in PQL.:
LIKE and INLIKE enable partial text matching and string comparisons in PQL.
Use LIKE and wildcards to match text strings like "credit card".
INLIKE allows combining multiple text criteria like "credit card" AND "survey offered"
I'm going to talk to you today about the like function in PQL for string comparison. It's often cumbersome to filter on items belonging to the same group, looking for things with the same text. So what we want to do is be able to determine automatically based on text if a certain input matches our determined string.
For an example, we're going to work with this call center data where it has activities/events based on steps in a call center process. We have starting the call, ending the call, authenticating caller, etc. What we want to be able to do is count up how many calls were regarding a credit card. To do that, we want to match all activities that have the text "credit card" in them.
So what we're going to do is count that. We're going to use a simple number count with the event and no transformation. We want to say if this event field has "credit card" in it, then we want to count all of those. So we'll do a sum, case when this event field is like 'credit card'.
There are two important things to note here. First, the 'credit card' text has to be in single quotes. And right now it's case sensitive. It's important to note that if there are no wildcards (%) this will be case insensitive. So capital C's will also match lowercase c's. So it's important to note you'll get potentially false positives there.
The percent sign wildcard indicates match any characters, any number of characters. So you could phrase it like all expressions starting with an arbitrary number of characters, ending with credit card. Because there's no % at the end, it must end with credit card, like we saw in our activities.
Then 1 else 0 end. So if it has credit card in it, we want to count it. This snippet of code using the like operator will now give us the number of activities that match.
What we really want to do is actually count the number of cases. So I like to do a bit of finagling here. Now we're counting the distinct number of call IDs, because the common table is the event log. It will go over each activity/event. If we did a normal count, we'd get too many cases. So now this only counts distinct call IDs where the event is like 'credit card'.
Now we see we have about 427,000 calls that match. Another part - what if we also want credit card and want to know if survey was offered? You can do the inlike operator. It's similar to an in operation with an array of strings. So array of strings in parentheses. We say okay, survey offer. With inlike you can do wildcards for credit card and just use 'survey offered'.
This number now increases to 469,000. So now we have the count of cases about credit card where a survey was offered.
Be sure to watch the other 20 videos in our series of tips to master Celonis! You can watch them here.
Don't miss out on weekly process mining tips either! We share actionable advice and tips from our work with you.
Get the process mining shortcuts and advice you don't even know you need (yet) now. CLICK NOW to add yourself to the list.
Comments