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
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")
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 |
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.
Column1
"Activities"."Case"
Column2
"Activities"."Activity"
Column3
ACTIVITY_LEAD("Activities"."Activity")
Column4
ACTIVITY_LEAD("Activities"."Activity",2)
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.
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
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 | 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' | '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' | 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 | 0 |
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 |
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
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 |
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
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 |
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 |
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
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 |
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")
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 |
Solution - Indirect Ping-Pong Cases
Filter
FILTER ACTIVATION_COUNT("Activities"."Activity") > 1
Column1
"Activities"."Case"
Column2
"Activities"."Activity"
Column3
MAX(ACTIVATION_COUNT("Activities"."Activity"))
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 |
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.
Column1
"Activities"."Case"
Column2
"Activities"."Activity"
Column3
ACTIVATION_COUNT("Activities"."Activity")
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 Ticket' | 1 |
Filter
FILTER ACTIVATION_COUNT("Activities"."Activity") > 1
Column1
"Activities"."Case"
Column2
"Activities"."Activity"
Column3
ACTIVATION_COUNT("Activities"."Activity")
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 |
Filter
FILTER ACTIVATION_COUNT("Activities"."Activity") > 1
Column1
"Activities"."Case"
Column2
"Activities"."Activity"
Column3
MAX(ACTIVATION_COUNT("Activities"."Activity"))
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 |