Description
This example shows how to calculate the throughput time of a machine with PQL.
In production use cases machine throughput time is often a number of interest. Calculating this number is not straight forward as the event log is usually built around the products which go through the assembly line and not the machine. Functions which are usually used for calculating the throughput time like SOURCE and TARGET or CALC_THROUGHPUT operate on case level. But a machine is normally involved in many cases. Therefore, LEAD and LAG can be used to calculate the duration of activities through a variety of cases. These functions can also be used to trace errors.
Duration of Stations
The duration of a station can be calculated by comparing the timestamp of an Activity to the timestamp of the next Activity, that can be identified using LEAD. The duration can also be calculated using LAG, but this would return the duration of the previous activity.
Filter
FILTER LEAD ( "Activities"."Timestamp" , PARTITION BY ( "Activities"."Activity" ) ) IS NOT NULL;
Column1
"Activities"."Activity"
Column2
HOURS_BETWEEN ( "Activities"."Timestamp" , LEAD ( "Activities"."Timestamp" , PARTITION BY ( "Activities"."Activity" ) ) )
Filter
FILTER LEAD ( "Activities"."Timestamp" , PARTITION BY ( "Activities"."Activity" ) ) IS NOT NULL;
Column1
"Activities"."Activity"
Column2
AVG ( HOURS_BETWEEN ( "Activities"."Timestamp" , LEAD ( "Activities"."Timestamp" , PARTITION BY ( "Activities"."Activity" ) ) ) )
Filter
FILTER LAG ( "Activities"."Timestamp" , PARTITION BY ( "Activities"."Activity" ) ) IS NOT NULL;
Filter
FILTER "Activities"."Activity" = 'B';
Column1
"Activities"."Case"
Column2
HOURS_BETWEEN ( LAG ( "Activities"."Timestamp" , PARTITION BY ( "Activities"."Activity" ) ) , "Activities"."Timestamp" )
Filter
FILTER ( "Activities"."Activity" ) LIKE 'Error%';
Column1
"Activities"."Activity"
Column2
LAG ( "Activities"."Activity" )
Activities
Case : INT | Activity : STRING | Timestamp : DATE |
---|---|---|
1 | 'A' | Mon Feb 01 2016 01:00:00.000 |
1 | 'B' | Mon Feb 01 2016 02:00:00.000 |
1 | 'Error X' | Mon Feb 01 2016 04:00:00.000 |
2 | 'A' | Mon Feb 01 2016 02:00:00.000 |
2 | 'Error Y' | Mon Feb 01 2016 03:00:00.000 |
2 | 'B' | Mon Feb 01 2016 04:00:00.000 |
3 | 'A' | Mon Feb 01 2016 04:00:00.000 |
3 | 'Error Z' | Mon Feb 01 2016 05:00:00.000 |
3 | 'B' | Mon Feb 01 2016 07:00:00.000 |
Column1
CASE WHEN LAG ( "Activities"."Activity" , PARTITION BY ( "Activities"."Case" ) ) LIKE 'Error%' THEN HOURS_BETWEEN ( LAG ( "Activities"."Timestamp" , PARTITION BY ( "Activities"."Case" ) ) , "Activities"."Timestamp" ) ELSE null END
Activities
Case : INT | Activity : STRING | Timestamp : DATE |
---|---|---|
1 | 'A' | Mon Feb 01 2016 01:00:00.000 |
1 | 'B' | Mon Feb 01 2016 02:00:00.000 |
1 | 'Error X' | Mon Feb 01 2016 04:00:00.000 |
2 | 'A' | Mon Feb 01 2016 02:00:00.000 |
2 | 'Error Y' | Mon Feb 01 2016 03:00:00.000 |
2 | 'B' | Mon Feb 01 2016 04:00:00.000 |
3 | 'A' | Mon Feb 01 2016 04:00:00.000 |
3 | 'Error Z' | Mon Feb 01 2016 05:00:00.000 |
3 | 'B' | Mon Feb 01 2016 07:00:00.000 |
Result
Column1 : FLOAT |
---|
null |
null |
null |
null |
null |
1.0 |
null |
null |
2.0 |
Column1
AVG ( CASE WHEN LAG ( "Activities"."Activity" , PARTITION BY ( "Activities"."Case" ) ) LIKE 'Error%' THEN HOURS_BETWEEN ( LAG ( "Activities"."Timestamp" , PARTITION BY ( "Activities"."Case" ) ) , "Activities"."Timestamp" ) ELSE null END )
Activities
Case : INT | Activity : STRING | Timestamp : DATE |
---|---|---|
1 | 'A' | Mon Feb 01 2016 01:00:00.000 |
1 | 'B' | Mon Feb 01 2016 02:00:00.000 |
1 | 'Error X' | Mon Feb 01 2016 04:00:00.000 |
2 | 'A' | Mon Feb 01 2016 02:00:00.000 |
2 | 'Error Y' | Mon Feb 01 2016 03:00:00.000 |
2 | 'B' | Mon Feb 01 2016 04:00:00.000 |
3 | 'A' | Mon Feb 01 2016 04:00:00.000 |
3 | 'Error Z' | Mon Feb 01 2016 05:00:00.000 |
3 | 'B' | Mon Feb 01 2016 07:00:00.000 |
Result
Column1 : FLOAT |
---|
1.5 |