Skip to main content

Celonis Product Documentation

ACTIVITY_LEAD
Description

ACTIVITY_LEAD returns the row of the activity table that follows the current row by offset number of rows within a case. The resulting column is a temporary column of the activity table.

Syntax
  ACTIVITY_LEAD ( activity_table.column [, offset ] )

  • activity_table.column: A column of an activity table.

  • offset: The number of non-NULL values following the current row in the provided column. 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.

Use Cases
Examples

[1]

Simple example for ACTIVITY_LEAD returning the next activity within a case. For the last row of each case, 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: