Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

ACTIVITY_LAG

Applies to: CELONIS 4.6

Description

ACTIVITY_LAG returns the activity from the row that precedes the current activity by offset number of rows within a case. Null activities are skipped.

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.

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

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'