Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

ACTIVITY_LEAD

Applies to: CELONIS 4.6 CELONIS 4.7

Description

ACTIVITY_LEAD returns the activity from the row that follows the current activity by offset number of rows within a case.

Syntax
ACTIVITY_LEAD ( table.column [, offset ] )

  • column: String column which has to be joinable to the activity table.

  • offset: The number of non-NULL activities following the current activity. The default value is 1.

NULL handling

The leading value for a NULL value is the same value as the leading value of the last non-NULL value. The offset parameter counts only non-NULL values.

Examples

[1] Simple example for ACTIVITY_LEAD returning the next activity within a case. If activity has no following activity, NULL is returned.

Query

Column1

ACTIVITY_LEAD ( "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

'B'

'C'

null

'B'

'C'

'D'

null

[2] Example for ACTIVITY_LEAD with offset = 2. ACTIVITY_LEAD returns the activity from the row that follows the current activity by 2 rows.

Query

Column1

ACTIVITY_LEAD ( "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

'C'

null

null

'C'

'D'

null

null

[3] Null activities are skipped for calculation of ACTIVITY_LEAD.

Query

Column1

ACTIVITY_LEAD ( "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

'B'

'C'

null

'B'

'B'

null

See also: