Applies to: CELONIS 4.7
Description
INDEX_ACTIVITY_ORDER returns the position of each activity within a case. Only not null activities are counted.
Syntax
INDEX_ACTIVITY_ORDER ( table.column )
- column: Column which has to be joinable to the activity table
NULL handling
If a row is NULL, then NULL will also be returned. NULL values do not influence the position of the non-NULL activities.
[1] INDEX_ACTIVITY_ORDER with null values.
Query
Column1
INDEX_ACTIVITY_ORDER ( "Table1"."ACTIVITY" )
Input
Table1
| CASE_ID : STRING | ACTIVITY : STRING | TIMESTAMP : DATE |
|---|---|---|
| '1' | 'A' | Tue Jan 01 2019 13:00:00.000 |
| '1' | 'B' | Tue Jan 01 2019 13:01:00.000 |
| '1' | null | Tue Jan 01 2019 13:02:00.000 |
| '2' | 'A' | Tue Jan 01 2019 13:03:00.000 |
| '2' | null | Tue Jan 01 2019 13:04:00.000 |
| '3' | 'A' | Tue Jan 01 2019 14:00:00.000 |
| '3' | null | Tue Jan 01 2019 14:05:00.000 |
| '3' | 'D' | Tue Jan 01 2019 14:10:00.000 |
| '3' | null | Tue Jan 01 2019 14:11:00.000 |
Output
Result
| Column1 : INT |
|---|
1 |
2 |
null |
1 |
null |
1 |
null |
2 |
null |
Examples
[2] Example with two cases in which each activity is taken into account.
Query
Column1
INDEX_ACTIVITY_ORDER ( "Table1"."ACTIVITY" )
[3] Example with two cases in which only B values are taken into account.
Query
Column1
INDEX_ACTIVITY_ORDER ( REMAP_VALUES ( "Table1"."ACTIVITY" , [ 'A' , NULL ] ) )