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 Pong 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 Ping 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


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

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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : 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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : STRINGColumn4 : 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'nullnull
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'nullnull


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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : STRINGColumn4 : STRINGColumn5 : INTColumn6 : 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''Do some work''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'nullnull
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
0
2
'Change Assigned Group''Review Ticket''Resolve Ticket'
1
0
2
'Review Ticket''Resolve Ticket'null
1
null
2
'Resolve Ticket'nullnull
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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : STRINGColumn4 : STRINGColumn5 : 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'nullnull
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'nullnull
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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : STRINGColumn4 : STRINGColumn5 : 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
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"
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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : STRINGColumn4 : STRINGColumn5 : 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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : 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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : 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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : 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 Ticket'
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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : 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
Activities
Case : INTActivity : STRINGTimestamp : 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
Output
Result
Column1 : INTColumn2 : STRINGColumn3 : INT
2
'Change Assigned Group'
3


  • No labels