Applies to: CELONIS 4.6 CELONIS 4.7
Description
ACTIVITY_LAG returns the activity from the row that precedes the current activity by offset number of rows within a case.
Syntax
ACTIVITY_LAG ( table.column [, offset ] )
- column: String column which has to be joinable to the activity table.
- offset: The number of non-NULL activities preceding the current activity. The default value is 1.
NULL handling
The lagging value for a NULL value is the same value as the lagging value for the next non-NULL value. The offset parameter counts only non-NULL values.
Examples
[1] Simple example for ACTIVITY_LAG returning the previous activity within a case. If activity has no previous
activity, NULL is returned.
Query
Column1
ACTIVITY_LAG ( "Table1"."activity" )
Output
Result
| Column1 : STRING |
|---|
| null |
| 'A' |
| 'B' |
| null |
| 'A' |
| 'B' |
| 'C' |
[2] Example for ACTIVITY_LAG with offset = 2. ACTIVITY_LAG returns the activity from the row that precedes the
current activity by 2 rows within a case.
Query
Column1
ACTIVITY_LAG ( "Table1"."activity" , 2 )
Output
Result
| Column1 : STRING |
|---|
| null |
| null |
| 'A' |
| null |
| null |
| 'A' |
| 'B' |
[3] Null activities are skipped for calculation of ACTIVITY_LAG.
Query
Column1
ACTIVITY_LAG ( "Table1"."activity" )
[4] ACTIVITY_LAG can be used for identifying activities causing errors. It is assumed that activities directly
followed by an error activity is defined as error causing activity. For identification of these activities,
the error activities are set to NULL within the CASE WHEN statement in the first step. Next, ACTIVITY_LAG is used to find the preceding activity that is not NULL.
By applying the FILTER statement,
only preceding activities of error activities are kept in the resulting table.
Query
Filter
FILTER "Activities"."activity" LIKE 'Error Type%';
Column1
"Activities"."activity"
Column2
ACTIVITY_LAG (
CASE WHEN "Activities"."activity" LIKE 'Error Type%' THEN NULL ELSE "Activities"."activity" END
)
Input
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 Type 1' | Mon Feb 01 2016 03:00:00.000 |
1 | 'C' | Mon Feb 01 2016 04:00:00.000 |
1 | 'Error Type 2' | Mon Feb 01 2016 05:00:00.000 |
1 | 'Error Type 3' | Mon Feb 01 2016 06:00:00.000 |
1 | 'D' | Mon Feb 01 2016 07:00:00.000 |
1 | 'E' | Mon Feb 01 2016 08:00:00.000 |
1 | 'Error Type 4' | Mon Feb 01 2016 09:00:00.000 |