Skip to main content
Solved

How to use LIKE in CALC_THROUGHPUT

  • February 21, 2022
  • 7 replies
  • 35 views
S
Level 2

Hi Community,

I have a question :

I'd like to use LIKE operator in CALC_THROUGHPUT function like this.

​---

FILTER (CALC_THROUGHPUT(LAST_OCCURRENCE[LIKE 'A_%']

TO FIRST_OCCURRENCE[LIKE 'B_%'],

REMAP_TIMESTAMPS("TABLE"."TIME_STAMP", DAYS)

)) <= 7 /* only within 7 days duration.*/

​---

but this doesn't work and I found the page* that is similar to my question.

​

This page is helpful, but in my case, the first half

of the first argument(LAST_OCCURRENCE[LIKE 'A_%']) requires LIKE operator as well.

​

Please let me know if you have any ideas.

Thanks.

​

*Reference : https://www.celopeers.com/s/question/0D50700000GC6lJCAT/using-ldquolikerdquo-in-process-functions-as-calcthroughput?t=1645409602164

Best answer by janpeter.van.d

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

    7 replies

    E
    Level 7
    Forum|alt.badge.img+13
    • eugene.em13
    • Level 7
    • February 21, 2022

    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

    S
    Level 2
    • so.yahat
    • Author
    • Level 2
    • February 22, 2022

    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.

    D
    Level 0

    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

     

     

     

    janpeter.van.d
    Level 12
    Forum|alt.badge.img+26
    • janpeter.van.d
    • Level 12
    • Answer
    • February 23, 2022

    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

    E
    Level 7
    Forum|alt.badge.img+13
    • eugene.em13
    • Level 7
    • February 23, 2022

    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_'

    janpeter.van.d
    Level 12
    Forum|alt.badge.img+26

    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_'

    Sharp, good addition!

    S
    Level 2
    • so.yahat
    • Author
    • Level 2
    • February 24, 2022

    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

    Community Code of ConductAccessibility statement