Skip to main content

Celonis Product Documentation

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
Examples

[1]

Simple example for ACTIVITY_LAG returning the previous activity within a case. For the first row of each case, NULL is returned:

Query

Column1 

         ACTIVITY_LAG ( "Table1"."activity" )

Input

Output

Table1

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

'C'

Mon Feb 01 2016 03:00:00.000

2

'A'

Mon Feb 01 2016 01:00:00.000

2

'B'

Mon Feb 01 2016 02:00:00.000

2

'C'

Mon Feb 01 2016 03:00:00.000

2

'D'

Mon Feb 01 2016 04:00:00.000

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 )

Input

Output

Table1

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

'C'

Mon Feb 01 2016 03:00:00.000

2

'A'

Mon Feb 01 2016 01:00:00.000

2

'B'

Mon Feb 01 2016 02:00:00.000

2

'C'

Mon Feb 01 2016 03:00:00.000

2

'D'

Mon Feb 01 2016 04:00:00.000

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" )

Input

Output

Table1

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

'C'

Mon Feb 01 2016 03:00:00.000

2

'A'

Mon Feb 01 2016 01:00:00.000

2

null

Mon Feb 01 2016 02:00:00.000

2

'B'

Mon Feb 01 2016 03:00:00.000

Result

Column1 : string

null

'A'

'B'

null

'A'

'A'

[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

Output

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

Result

Column1 : string

Column2 : string

'Error Type 1'

'B'

'Error Type 2'

'C'

'Error Type 3'

'C'

'Error Type 4'

'E'

See also: