Hi So,
Documentation doesn't mention anything for using LIKE operator with CALC_THROUGHPUT. I believe for the expression to work you have to give it concrete values, i.e. full activity names.
You can, however, use multiple FILTER statements and define each pair of activities separately, provided that each pair is spelled out without LIKE operator.
Hope this helps!
Eugene
Hello Eugene,
Thanks for the reply.
If the situation is below, what would be the formula?
------
Activity name : "TABLE"."ACTIVITY" (values are like {A_aa, A_ab, B_aa, B_ab...} (The upper case letter is a category, the lower case letter is a sub category.))
Timestamp : "TABLE"."TIME_STAMP"
What I Want to do : Filter cases where starting from the last occurrence starting with 'A' to the first occurrence starting with 'B' and the duration is within 7 days.
------
If my explanation is messed up, I am sorry.
regards So.
Hello,
First: I think that if you want to have both a category and subcategory it should be done in separate column to avoid such problems. Default practice in such situation is having "ACTIVITY_EN" (Or just "ACTIVITY") and "ACTIVITY_DETAILS" column for the subcategory.
Second: If it cannot be changed, (but that's really bad idea, that will cause trouble in the future) I would try to use MATCH_PROCES_REGEX or MATCH_ROCESS and then compared the eventtime just by using lead or lag function. Check: <your celonis link>/help/display/CIBC/MATCH_PROCESS_REGEX or <your celonis link>/help/display/CIBC/MATCH_PROCESS. Previously I've tried also to use standard throughput time calculation mixed with the PATINDEX and RIGHT functions to cut the "A" from ?A_XYZ" but it seemed to be dead end.
If you will calculate throughput time in days for cases it should be very simple to just add additional CASE WHEN statement with >7 check. Hope that helps a bit, let us know it that approach worked. 
Best Regards,
Mateusz Dudek
Hi @so.yahat,
Maybe the CALC_THROUGHPUT Operator is not the best fit here. You could achieve the same if I'm not mistaken using the PU_FIRST/PU_LAST operators, together with the DATEDIFF operator. This would result in something like:
FILTER
DATEDIFF(
-- timestamp difference in days
DD,
-- For each case (based on the case table), take the last activity (based on eventtime) where the activity name is like 'A_%'
PU_LAST (
"CASE_TABLE",
"ACTIVITY_TABLE"."EVENTTIME",
"ACTIVITY_TABLE"."ACTIVITY_EN" LIKE 'A_%'),
PU_FIRST (
"CASE_TABLE",
"ACTIVITY_TABLE"."EVENTTIME",
"ACTIVITY_TABLE"."ACTIVITY_EN" LIKE 'B_%')
) <= 7
Let me know if this helps.
Kind regards,
Jan-peter
Hi @so.yahat,
Maybe the CALC_THROUGHPUT Operator is not the best fit here. You could achieve the same if I'm not mistaken using the PU_FIRST/PU_LAST operators, together with the DATEDIFF operator. This would result in something like:
FILTER
DATEDIFF(
-- timestamp difference in days
DD,
-- For each case (based on the case table), take the last activity (based on eventtime) where the activity name is like 'A_%'
PU_LAST (
"CASE_TABLE",
"ACTIVITY_TABLE"."EVENTTIME",
"ACTIVITY_TABLE"."ACTIVITY_EN" LIKE 'A_%'),
PU_FIRST (
"CASE_TABLE",
"ACTIVITY_TABLE"."EVENTTIME",
"ACTIVITY_TABLE"."ACTIVITY_EN" LIKE 'B_%')
) <= 7
Let me know if this helps.
Kind regards,
Jan-peter
That should work, one thing I would change is either use PU_MIN/PU_MAX or add the sorting by timestamp for PU_FIRST/PU_LAST.
@so.yahat - please note that this query will take the earliest timestamp of all activities that start with 'A_' and the latest activity of all activities that start with 'B_'
That should work, one thing I would change is either use PU_MIN/PU_MAX or add the sorting by timestamp for PU_FIRST/PU_LAST.
@so.yahat - please note that this query will take the earliest timestamp of all activities that start with 'A_' and the latest activity of all activities that start with 'B_'
Worked completely!
My thanks to both of you!
Regards,
So
