Skip to main content
Solved

Filtering based on ACTIVITY_LAG does not work

  • October 3, 2023
  • 4 replies
  • 3 views
K
Level 4

I am trying to screen the cases where we set order block and immediately release without any activities in the middle. The idea is to calcuate how much time we wasted on those cases.

 

To do that: In the activities table, I want to partition along column "Change_to" in such a way:

 

When "Change_from" = 'ZA' (which incdicates release of block)

Then look at the immediate preceding rows: if the column "Change_to" = 'ZA' (indicate set block)

 

Then calculate the datediff between those rows.

 

This is my code:

 

SUM(

 

CASE WHEN ("_CEL_O2C_ACTIVITIES"."CHANGED_FROM" = 'ZA' AND ACTIVITY_LAG ("_CEL_O2C_ACTIVITIES"."CHANGED_TO", 1) = 'ZA')

 

 THEN

 

 DATEDIFF ( MI, ACTIVITY_LAG("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."EVENTTIME")

END)

 

It seems that the above code just ignore the bolded part in the casewhen statement. It calculates the datediff even when the direct preceding row does not have "Change to" = 'ZA'.

 

Any ideas whats wrong with the code?

Best answer by devesh.jinda12

use of coalesce function will solve the issue for you, as not all the activity have value in changed from and changed to column, it will take last null value.

 

please use the below code, it should solve the problem for you

 

SUM(

 

CASE WHEN ("_CEL_O2C_ACTIVITIES"."CHANGED_FROM" = 'ZA' AND ACTIVITY_LAG (COALESCE("_CEL_O2C_ACTIVITIES"."CHANGED_TO",''), 1) = 'ZA')

 

 THEN

 

 DATEDIFF ( MI, ACTIVITY_LAG("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."EVENTTIME")

END)

    4 replies

    abhishek.chatu14
    Level 11
    Forum|alt.badge.img+4

    This should work not sure what is the issue here try something like this

     

    SUM(

    CASE WHEN ("_CEL_O2C_ACTIVITIES"."CHANGED_FROM" = 'ZA' 

    THEN

    CASE WHEN ACTIVITY_LAG ("_CEL_O2C_ACTIVITIES"."CHANGED_TO", 1) = 'ZA')

    THEN

    DATEDIFF ( MI, ACTIVITY_LAG("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."EVENTTIME")

    END END)

    K
    Level 4
    • kinsley.le
    • Author
    • Level 4
    • 8 replies
    • October 4, 2023

    This should work not sure what is the issue here try something like this

     

    SUM(

    CASE WHEN ("_CEL_O2C_ACTIVITIES"."CHANGED_FROM" = 'ZA' 

    THEN

    CASE WHEN ACTIVITY_LAG ("_CEL_O2C_ACTIVITIES"."CHANGED_TO", 1) = 'ZA')

    THEN

    DATEDIFF ( MI, ACTIVITY_LAG("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."EVENTTIME")

    END END)

    Hi Abhishek,

     

    I did try this. The code behaves the same way, not filtering the value requested in ACTIVITY_LAG statement.

    D
    Level 2
    • devesh.jinda12
    • Level 2
    • 4 replies
    • Answer
    • October 9, 2023

    use of coalesce function will solve the issue for you, as not all the activity have value in changed from and changed to column, it will take last null value.

     

    please use the below code, it should solve the problem for you

     

    SUM(

     

    CASE WHEN ("_CEL_O2C_ACTIVITIES"."CHANGED_FROM" = 'ZA' AND ACTIVITY_LAG (COALESCE("_CEL_O2C_ACTIVITIES"."CHANGED_TO",''), 1) = 'ZA')

     

     THEN

     

     DATEDIFF ( MI, ACTIVITY_LAG("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."EVENTTIME")

    END)

    K
    Level 4
    • kinsley.le
    • Author
    • Level 4
    • 8 replies
    • October 9, 2023

    use of coalesce function will solve the issue for you, as not all the activity have value in changed from and changed to column, it will take last null value.

     

    please use the below code, it should solve the problem for you

     

    SUM(

     

    CASE WHEN ("_CEL_O2C_ACTIVITIES"."CHANGED_FROM" = 'ZA' AND ACTIVITY_LAG (COALESCE("_CEL_O2C_ACTIVITIES"."CHANGED_TO",''), 1) = 'ZA')

     

     THEN

     

     DATEDIFF ( MI, ACTIVITY_LAG("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."EVENTTIME")

    END)

    @Devesh: Thank you! You are right. The issue with my code rooted from the data. And your code works perfectly 👍


    Community Code of ConductAccessibility statement