Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

Ping pong cases

Applies to: CELONIS 4.6

Description

This example shows how to identify ping pong cases with PQL.

In IT ticketing systems, an incident should ideally be resolved quickly and without interference of too many departments or teams. However, so called ping ong cases where a ticket is repeatedly going back and forth between support teams or groups appear quite often in reality. To ensure a smooth handling of incidents and to reduce throughput times, avoiding unnecessary ping pong cases is crucial.

Ping pong cases can be classified into two categories:

  • Direct ping pong cases: The same activity appears (at least) two times with only one other activity in between, e.g. Change Assigned Group → Review Ticket → Change Assigned Group

  • Indirect ping pong cases: The same activity appears (at least) two times with more than one other activity in between, e.g. Change Assigned Group → Review Ticket → Do some work → Change Assigned Group

Challenge

For each ping pong case, show the activities that caused the ping pong behavior together with the number of ping pong occurrences.

For direct ping pong cases that means to show the activities that appear (at least) two times with only one other activity in between concatenated with the respective in-between activity and again the ping pong"activity. In a separate column show the number of ping pong occurrences.

For indirect ping pong cases show the activities that appear (at least) two times with more than one other activity in between and, in a separate column, the number of occurrences

Solution - Direct ping pong cases

Query

[1] The direct ping pong cases can be identified using the following query. In this example, case 1 shows direct ping Ppong behavior of activities ('Change Assigned Group' appears 3 times with only one activity in between), and case 2 does not.

Filter

FILTER ACTIVITY_LEAD("Activities"."Activity", 2) IS NOT NULL

Filter

FILTER "Activities"."Activity" = ACTIVITY_LEAD("Activities"."Activity", 2) AND "Activities"."Activity" != ACTIVITY_LEAD("Activities"."Activity")Column1"Activities"."Case"Column2"Activities"."Activity" || ' - ' || ACTIVITY_LEAD("Activities"."Activity") || ' - ' || ACTIVITY_LEAD("Activities"."Activity",2)Column3COUNT("Activities"."Activity")

Column1

"Activities"."Case"

Column2

"Activities"."Activity" || ' - ' || ACTIVITY_LEAD("Activities"."Activity") || ' - ' || ACTIVITY_LEAD("Activities"."Activity",2)Column3COUNT("Activities"."Activity")InputActivitiesCase : INTActivity : STRINGTimestamp : DATE1'Create Ticket'Mon Feb 01 2016 01:00:00.0001'Assign Ticket'Mon Feb 01 2016 02:00:00.0001'Change Assigned Group'Mon Feb 01 2016 03:00:00.0001'Review Ticket'Mon Feb 01 2016 04:00:00.0001'Change Assigned Group'Mon Feb 01 2016 05:00:00.0001'Do some work'Mon Feb 01 2016 06:00:00.0001'Change Assigned Group'Mon Feb 01 2016 07:00:00.0001'Review Ticket'Mon Feb 01 2016 08:00:00.0001'
Change Assigned Group'Mon Feb 01 2016 09:00:00.0001'Resolve Ticket'Mon Feb 01 2016 10:00:00.0002'Create Ticket'Mon Feb 01 2016 01:00:00.0002'Assign Ticket'Mon Feb 01 2016 02:00:00.0002'Change Assigned Group'Mon Feb 01 2016 03:00:00.0002'Change Assigned Group'Mon Feb 01 2016 04:00:00.0002'Review Ticket'Mon Feb 01 2016 05:00:00.0002'Resolve Ticket'Mon Feb 01 2016 06:00:00.000OutputResultColumn1 : INTColumn2 : STRINGColumn3 : INT1'Change Assigned Group - Do some work - Change Assigned Group'11'Change Assigned Group - Review Ticket - Change Assigned Group'2

Column3

COUNT("Activities"."Activity")

Input

Output

Activities

TicketId : INT

Activity : STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 04:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 05:00:00.000

1

'Do some work'

Mon Feb 01 2016 06:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 07:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 08:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 09:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 05:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

Result

Column1 : INT

Column2 : STRING

Column3 : INT

1

'Change Assigned Group - Do some work - Change Assigned Group'

1

1

'Change Assigned Group - Review Ticket - Change Assigned Group'

2

Step-by-step solution - Direct ping-pong cases

In order to find direct ping pong cases, we need to compare each activity with the following activities. This can be done with the ACTIVITY_LEAD operator. Now let's take a closer look on this operator.

[2] To be able to compare the current activity with the next and second next activity, we need to have them in one line. We can do so by using ACTIVITY_LEAD.

Query

Column1

"Activities"."Case"

Column2

"Activities"."Activity"

Column3

ACTIVITY_LEAD("Activities"."Activity")

Column4

ACTIVITY_LEAD("Activities"."Activity",2)

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 04:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 05:00:00.000

1

'Do some work'

Mon Feb 01 2016 06:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 07:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 08:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 09:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 05:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

Result

Column1 : INT

Column2 : STRING

Column3 : STRING

Column4 : STRING

1

'Create Ticket'

'Assign Ticket'

'Change Assigned Group'

1

'Assign Ticket'

'Change Assigned Group'

'Review Ticket'

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

'Review Ticket'

'Change Assigned Group'

'Do some work'

1

'Change Assigned Group'

'Do some work'

'Change Assigned Group'

1

'Do some work'

'Change Assigned Group'

'Review Ticket'

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

'Review Ticket'

'Change Assigned Group'

'Resolve Ticket'

1

'Change Assigned Group'

'Resolve Ticket'

null

1

'Resolve Ticket'

null

null

2

'Create Ticket'

'Assign Ticket'

'Change Assigned Group'

2

'Assign Ticket'

'Change Assigned Group'

'Change Assigned Group'

2

'Change Assigned Group'

'Change Assigned Group'

'Review Ticket'

2

'Change Assigned Group'

'Review Ticket'

'Resolve Ticket'

2

'Review Ticket'

'Resolve Ticket'

null

2

'Resolve Ticket'

null

null

After having the necessary activity information in one line, we can easily build up on that with simple CASE WHEN statements.

[3] As stated in the definition, in direct ping pong cases an activity appears (at least) two times with only one other activity in between. Therefore, we need to test whether the second next activity is equal to the current activity. At the same time, the next activity must be different to the current activity. Now that we have everything in one line, we can use CASE WHEN statements to compare the activities.

Query

Column1

"Activities"."Case"

Column2

"Activities"."Activity"

Column3

ACTIVITY_LEAD("Activities"."Activity")

Column4

ACTIVITY_LEAD("Activities"."Activity",2)

Column5

CASE WHEN "Activities"."Activity" != ACTIVITY_LEAD("Activities"."Activity") THEN 1 ELSE 0 END

Column6

CASE WHEN  "Activities"."Activity" = ACTIVITY_LEAD("Activities"."Activity", 2) THEN 1 ELSE 0 END

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 04:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 05:00:00.000

1

'Do some work'

Mon Feb 01 2016 06:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 07:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 08:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 09:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 05:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

Result

Column1 : STRING

Column2 : STRING

Column3 : STRING

Column4 : STRING

Column5 : INT

Column6 : INT

1

'Create Ticket'

'Assign Ticket'

'Change Assigned Group'

1

0

1

'Assign Ticket'

'Change Assigned Group'

'Review Ticket'

1

0

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

1

1

'Review Ticket'

'Change Assigned Group'

'Do some work'

1

0

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

1

1

'Do some work'

'Change Assigned Group'

'Review Ticket'

1

0

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

1

1

'Review Ticket'

'Change Assigned Group'

'Resolve Ticket'

1

0

1

'Change Assigned Group'

'Resolve Ticket'

null

1

null

1

'Resolve Ticket'

null

null

null

null

2

'Create Ticket'

'Assign Ticket'

'Change Assigned Group'

1

0

2

'Assign Ticket'

'Change Assigned Group'

'Change Assigned Group'

1

0

2

'Change Assigned Group'

'Change Assigned Group'

'Review Ticket'

0

o

2

'Change Assigned Group'

'Review Ticket'

'Resolve Ticket'

1

0

2

'Review Ticket'

'Resolve Ticket'

null

1

null

2

'Resolve Ticket'

null

null

null

null

[4] Next, we are combining the two CASE WHEN statements together.

Query

Column1

"Activities"."Case"

Column2

"Activities"."Activity"

Column3

ACTIVITY_LEAD("Activities"."Activity")

Column4

ACTIVITY_LEAD("Activities"."Activity",2)

Column5

CASE WHEN "Activities"."Activity" = ACTIVITY_LEAD("Activities"."Activity", 2) AND "Activities"."Activity" != ACTIVITY_LEAD("Activities"."Activity") 
THEN 1 ELSE 0 END

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 04:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 05:00:00.000

1

'Do some work'

Mon Feb 01 2016 06:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 07:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 08:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 09:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 05:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

Result

Column1 : INT

Column2 : STRING

Column3 : STRING

Column4 : STRING

Column5 : INT

1

'Create Ticket'

'Assign Ticket'

'Change Assigned Group'

0

1

'Assign Ticket'

'Change Assigned Group'

'Review Ticket'

0

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

1

'Review Ticket'

'Change Assigned Group'

'Do some work'

0

1

'Change Assigned Group'

'Do some work'

'Change Assigned Group'

1

1

'Do some work'

'Change Assigned Group'

'Review Ticket'

0

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

1

'Review Ticket'

'Change Assigned Group'

'Resolve Ticket'

0

1

'Change Assigned Group'

'Resolve Ticket'

null

null

1

'Resolve Ticket'

null

null

null

2

'Create Ticket'

'Assign Ticket'

'Change Assigned Group'

0

2

'Assign Ticket'

'Change Assigned Group'

'Change Assigned Group'

0

2

'Change Assigned Group'

'Change Assigned Group'

'Review Ticket'

0

2

'Change Assigned Group'

'Review Ticket'

'Resolve Ticket'

0

2

'Review Ticket'

'Resolve Ticket'

null

null

2

'Resolve Ticket'

null

null

null

[5] In order to get rid of the NULL values, in the next step, we are applying a filter to the table.

Query

Filter

FILTER ACTIVITY_LEAD("Activities"."Activity", 2) IS NOT NULL

Column1

"Activities"."Case"

Column2

"Activities"."Activity"

Column3

ACTIVITY_LEAD("Activities"."Activity")

Column4

ACTIVITY_LEAD("Activities"."Activity",2)

Column5

CASE WHEN "Activities"."Activity" = ACTIVITY_LEAD("Activities"."Activity", 2) AND "Activities"."Activity" != ACTIVITY_LEAD("Activities"."Activity") THEN 1 ELSE 0 END

Input

Output

Activities

Case : INT

Activity :

STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 04:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 05:00:00.000

1

'Do some work'

Mon Feb 01 2016 06:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 07:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 08:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 09:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 05:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

Result

Column1 : INT

Column2 : STRING

Column3 : STRING

Column4 : STRING

Column5 : INT

1

'Create Ticket'

'Assign Ticket'

'Change Assigned Group'

0

1

'Assign Ticket'

'Change Assigned Group'

'Review Ticket'

0

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

1

'Review Ticket'

'Change Assigned Group'

'Do some work'

0

1

'Change Assigned Group'

'Do some work'

'Change Assigned Group'

1

1

'Do some work'

'Change Assigned Group'

'Review Ticket'

0

1

'Change Assigned Group'

'Do some work'

'Change Assigned Group'

1

1

'Do some work'

'Change Assigned Group'

'Review Ticket'

0

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

1

'Review Ticket'

'Change Assigned Group'

'Resolve Ticket'

0

2

'Create Ticket'

'Assign Ticket'

'Change Assigned Group'

0

2

'Assign Ticket'

'Change Assigned Group'

'Change Assigned Group'

0

2

'Change Assigned Group'

'Change Assigned Group'

'Review Ticket'

0

2

'Change Assigned Group'

'Review Ticket'

'Resolve Ticket'

0

[6] In the end, we only want to keep the rows which are 1. Therefore, we are moving the CASE WHEN statement into the filter.

Query

Filter

FILTER ACTIVITY_LEAD("Activities"."Activity", 2) IS NOT NULL

Filter

FILTER "Activities"."Activity" = ACTIVITY_LEAD("Activities"."Activity", 2) AND "Activities"."Activity" != ACTIVITY_LEAD("Activities"."Activity")Column1"Activities"."Case"Column2"Activities"."Activity"Column3ACTIVITY_LEAD("Activities"."Activity")Column4ACTIVITY_LEAD("Activities"."Activity",2)Column5CASE WHEN "Activities"."Activity" = ACTIVITY_LEAD("Activities"."Activity", 2) AND "Activities"."Activity" != ACTIVITY_LEAD("Activities"."Activity") THEN 1 ELSE 0 ENDInputActivitiesCase : INTActivity : STRINGTimestamp : DATE1'Create Ticket'Mon Feb 01 2016 01:00:00.0001'Assign Ticket'Mon Feb 01 2016 02:00:00.0001'Change Assigned Group'Mon Feb 01 2016 03:00:00.0001'Review Ticket'Mon Feb 01 2016 04:00:00.0001'Change Assigned Group'Mon Feb 01 2016 05:00:00.0001'Do some work'Mon Feb 01 2016 06:00:00.0001'Change Assigned Group'Mon Feb 01 2016 07:00:00.0001'Review Ticket'Mon Feb 01 2016 08:00:00.0001'Change Assigned Group'Mon Feb 01 2016 09:00:00.0001'Resolve Ticket'Mon Feb 01 2016 10:00:00.0002'Create Ticket'Mon Feb 01 2016 01:00:00.0002'Assign Ticket'Mon Feb 01 2016 02:00:00.0002'Change Assigned Group'Mon Feb 01 2016 03:00:00.0002'Change Assigned Group'Mon Feb 01 2016 04:00:00.0002'Review Ticket'Mon Feb 01 2016 05:00:00.0002'Resolve Ticket'Mon Feb 01 2016 06:00:00.000OutputResultColumn1 : INTColumn2 : STRINGColumn3 : STRINGColumn4 : STRINGColumn5 : INT1'Change Assigned Group''Review Ticket''Change Assigned Group'11'Change Assigned Group''Do some work''Change Assigned Group'11'Change Assigned Group''Review Ticket''Change Assigned Group'1

Column1

"Activities"."Case"

Column2

"Activities"."Activity"

Column3

ACTIVITY_LEAD("Activities"."Activity")

Column4

ACTIVITY_LEAD("Activities"."Activity",2)

Column5

CASE WHEN "Activities"."Activity" = ACTIVITY_LEAD("Activities"."Activity", 2) AND "Activities"."Activity" != ACTIVITY_LEAD("Activities"."Activity") THEN 1 ELSE 0 ENDInputActivitiesCase : INTActivity : STRINGTimestamp : DATE1'Create Ticket'Mon Feb 01 2016 01:00:00.0001'Assign Ticket'Mon Feb 01 2016 02:00:00.0001'Change Assigned Group'Mon Feb 01 2016 03:00:00.0001'Review Ticket'Mon Feb 01 2016 04:00:00.0001'Change Assigned Group'Mon Feb 01 2016 05:00:00.0001'Do some work'Mon Feb 01 2016 06:00:00.0001'Change Assigned Group'Mon Feb 01 2016 07:00:00.0001'Review Ticket'Mon Feb 01 2016 08:00:00.0001'Change Assigned Group'Mon Feb 01 2016 09:00:00.0001'Resolve Ticket'Mon Feb 01 2016 10:00:00.0002'Create Ticket'Mon Feb 01 2016 01:00:00.0002'Assign Ticket'Mon Feb 01 2016 02:00:00.0002'Change Assigned Group'Mon Feb 01 2016 03:00:00.0002'Change Assigned Group'Mon Feb 01 2016 04:00:00.0002'Review Ticket'Mon Feb 01 2016 05:00:00.0002'Resolve Ticket'Mon Feb 01 2016 06:00:00.000OutputResultColumn1 : INTColumn2 : STRINGColumn3 : STRINGColumn4 : STRINGColumn5 : INT1'Change Assigned Group''Review Ticket''Change Assigned Group'11'Change Assigned Group''Do some work''Change Assigned Group'11'Change Assigned Group''Review Ticket''Change Assigned Group'1

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 05:00:00.000

1

'Do some work'

Mon Feb 01 2016 06:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 07:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 08:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 09:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 05:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

Result

Column1 : INT

Column2 : STRING

Column3 : STRING

Column4 : STRING

Column5 : INT

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

1

'Change Assigned Group'

'Do some work'

'Change Assigned Group'

1

1

'Change Assigned Group'

'Review Ticket'

'Change Assigned Group'

1

[7] In the last step, we are replacing the CASE WHEN statement in the table by a COUNT aggregation and concatenate the columns with activity names.

Query

Filter

FILTER ACTIVITY_LEAD("Activities"."Activity", 2) IS NOT NULL

Filter

FILTER "Activities"."Activity" = ACTIVITY_LEAD("Activities"."Activity", 2) AND "Activities"."Activity" != ACTIVITY_LEAD("Activities"."Activity")

Column1

"Activities"."Case"

Column2

"Activities"."Activity" || ' - ' || ACTIVITY_LEAD("Activities"."Activity") || ' - ' || ACTIVITY_LEAD("Activities"."Activity",2)

Column3

COUNT("Activities"."Activity")

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 04:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 05:00:00.000

1

'Do some work'

Mon Feb 01 2016 06:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 07:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 08:00:00.000

1

'Do some work'

Mon Feb 01 2016 09:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 05:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

Result

Column1 : INT

Column2 : STRING

Column3 : INT

1

'Change Assigned Group - Do some work - Change Assigned Group'

1

1

'Change Assigned Group - Review Ticket - Change Assigned Group'

2

Solution - Indirect ping pong cases

[8] The indirect ping pong cases can be identified using the following query. In this example, case 1 does not show any ping pong behavior of activities, but case 2 is an indirect ping pong case ('Change Assigned Group' appears 3 times with more than one activity in between).

Query

Filter

FILTER ACTIVATION_COUNT("Activities"."Activity") > 1

Column1

"Activities"."Case"

Column2

"Activities"."Activity"

Column3

MAX(ACTIVATION_COUNT("Activities"."Activity"))

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 03:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

1

'Do some work'

Mon Feb 01 2016 05:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

1

'Close Ticket'

Mon Feb 01 2016 07:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 04:00:00.000

2

'Do some work'

Mon Feb 01 2016 05:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 06:00:00.000

2

'Do some other work'

Mon Feb 01 2016 07:00:00.000

2

'Change Ticket Category'

Mon Feb 01 2016 08:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 09:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Cloase Ticket'

Mon Feb 01 2016 11:00:00.000

Result

Column1 : INT

Column2 : STRING

Column3 : INT

2

'Change Assigned Group'

3

Step-by-step solution - Indirect ping pong cases

Now let's take a closer look on the operator ACTIVATION_COUNT which is used in the solution.

[9] For identification of indirect ping pong cases, we first need to identify activities that occur multiple times within a case. We can do so by using  ACTIVATION_COUNT.

Query

Column1

"Activities"."Case"

Column2

"Activities"."Activity"

Column3

ACTIVATION_COUNT("Activities"."Activity")

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 03:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

1

'Do some work'

Mon Feb 01 2016 05:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

1

'Close Ticket'

Mon Feb 01 2016 07:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 04:00:00.000

2

'Do some work'

Mon Feb 01 2016 05:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 06:00:00.000

2

'Do some other work'

Mon Feb 01 2016 07:00:00.000

2

'Change Ticket Category'

Mon Feb 01 2016 08:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 09:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Close Ticket'

Mon Feb 01 2016 11:00:00.000

Result

Column1: INT

Column2 : STRING

Column3 : INT

1

'Create Ticket'

1

1

'Assign Ticket'

1

1

'Review Ticket'

1

1

'Change Assigned Group'

1

1

'Do some work'

1

1

'Resolve Ticket'

1

1

'Close Ticket'

1

2

'Create Ticket'

1

2

'Assign Ticket'

1

2

'Change Assigned Group'

1

2

'Review Ticket'

1

2

'Do some work'

1

2

'Change Assigned Group'

2

2

'Do some other work'

1

2

'Change Ticket Category'

1

2

'Change Assigned Group'

3

2

'Resolve Ticket'

1

2

'Close Tiket'

1

[10] Next, we want to remain only those activities that have an ACTIVATION_COUNT greater that 1. We can do so by using a filter on the table.

Query

Filter

FILTER ACTIVATION_COUNT("Activities"."Activity") > 1

Column1

"Activities"."Case"

Column2

"Activities"."Activity"

Column3

ACTIVATION_COUNT("Activities"."Activity")

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 03:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

1

'Do some work'

Mon Feb 01 2016 05:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

1

'Close Ticket'

Mon Feb 01 2016 07:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 04:00:00.000

2

'Do some work'

Mon Feb 01 2016 05:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 06:00:00.000

2

'Do some other work'

Mon Feb 01 2016 07:00:00.000

2

'Change Ticket Category'

Mon Feb 01 2016 08:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 09:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Close Ticket'

Mon Feb 01 2016 11:00:00.000

Result

Column1 : INT

Column2 : STRING

Column3 : INT

2

'Change Assigned Group'

2

2

'Change Assigned Group'

3

[11] In the last step, we are using the MAX aggregation function to receive only the maximum number of occurrence for the activity 'Change Assigned Group'.

Query

Filter

FILTER ACTIVATION_COUNT("Activities"."Activity") > 1

Column1

"Activities"."Case"

Column2

"Activities"."Activity"

Column3

MAX(ACTIVATION_COUNT("Activities"."Activity"))

Input

Output

Activities

Case : INT

Activity : : STRING

Timestamp : DATE

1

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

1

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

1

'Review Ticket'

Mon Feb 01 2016 03:00:00.000

1

'Change Assigned Group'

Mon Feb 01 2016 04:00:00.000

1

'Do some work'

Mon Feb 01 2016 05:00:00.000

1

'Resolve Ticket'

Mon Feb 01 2016 06:00:00.000

1

'Close Ticket'

Mon Feb 01 2016 07:00:00.000

2

'Create Ticket'

Mon Feb 01 2016 01:00:00.000

2

'Assign Ticket'

Mon Feb 01 2016 02:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 03:00:00.000

2

'Review Ticket'

Mon Feb 01 2016 04:00:00.000

2

'Do some work'

Mon Feb 01 2016 05:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 06:00:00.000

2

'Do some other work'

Mon Feb 01 2016 07:00:00.000

2

'Change Ticket Category'

Mon Feb 01 2016 08:00:00.000

2

'Change Assigned Group'

Mon Feb 01 2016 09:00:00.000

2

'Resolve Ticket'

Mon Feb 01 2016 10:00:00.000

2

'Close Ticket'

Mon Feb 01 2016 11:00:00.000

Result

Column1 : INT

Column2 : STRING

Column3 : INT

2

'Change Assigned Group'

3