ACTIVITY_LAG
Description
ACTIVITY_LAG returns the row of the activity table that precedes the current row by offset number of rows within a case. The resulting column is a temporary column of the activity table.
Syntax
ACTIVITY_LAG ( activity_table.column [, offset ] )
activity_table.column: A column of an activity table.
offset: The number of non-NULL values preceding the current row in the provided column. 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.
Use Cases
ACTIVITY_LAG
can be used for Activity SequencesACTIVITY_LAG
can be used for Triggering Activities.ACTIVITY_LAG
can be used for Ping-Pong-Cases.
Examples
[1] Simple example for ACTIVITY_LAG returning the previous activity within a case. For the first row of each case, NULL is returned: | ||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||
|
[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: | ||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||
|
[3] Null activities are skipped for calculation of ACTIVITY_LAG: | ||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||
|
[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. | ||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||
|