Skip to main content
Solved

How to identify a variants have duplicated activity?

  • Sep 6, 2023 9:12 AM UTC
  • 2 replies
  • 139 views

Hi all expert,

 

I would like to identify all case if it has duplicated continuous activity. 

This is example of my case below. 

...A -> A -> B ...(duplicate)

...A -> B -> A... (non-duplicate)

 

Does anybody know good PQL or way how to do this?

Best answer by Anonymous

I would use the Lead statement for this:

 

 

sum(

Case

when LEAD ( Activitytable.activitycolumn , ORDER BY ( activitytable.timestamp) )

= Activitytable.activitycolumn

then 1.0

else 0.0

end

)

 

Each case that has 1 or more, has duplicate activities.

View original

2 replies

  • Answer
  • Sep 6, 2023 11:12 AM UTC

I would use the Lead statement for this:

 

 

sum(

Case

when LEAD ( Activitytable.activitycolumn , ORDER BY ( activitytable.timestamp) )

= Activitytable.activitycolumn

then 1.0

else 0.0

end

)

 

Each case that has 1 or more, has duplicate activities.


  • Sep 7, 2023 7:04 AM UTC

I would use the Lead statement for this:

 

 

sum(

Case

when LEAD ( Activitytable.activitycolumn , ORDER BY ( activitytable.timestamp) )

= Activitytable.activitycolumn

then 1.0

else 0.0

end

)

 

Each case that has 1 or more, has duplicate activities.

Thank you for your response. Just to make sure, by using the Lead function, does this formula refer to the next Activity name that is ordered by to determine if they are the same or not?