Applies to:  CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 


This operator crops cases to a range of activities. All values outside this range are mapped to null. Values within the range are kept, in contrast to CALC_CROP which maps them to 1.

Syntax

CALC_CROP_TO_NULL(<begin_range_specifier> TO <end_range_specifier>, <column>)
begin_range_specifier = CASE_START | FIRST_OCCURRENCE [ <activity_name> ] | LAST_OCCURRENCE [<activity_name>]
end_range_specifier = CASE_END | FIRST_OCCURRENCE [ <activity_name> ] | LAST_OCCURRENCE [<activity_name>]
column : Any string column which can be joined to the activity table

Range Specifier

FIRST_OCCURRENCE['activity'] : First occurrence of the specified activity type. LAST_OCCURRENCE['activity'] : Last occurrence of the specified activity type. CASE_START : Range begins at the start of the case. CASE_END : Range ends at the end of the case

Examples


Simple example with one matching (In case 1 B comes before C) and one not matching (In case 2 there is no C after B) case.


Query
Column1
CALC_CROP_TO_NULL(FIRST_OCCURRENCE [ 'B' ] TO LAST_OCCURRENCE ['C'], "Table1"."activity")
Input
Table1
case : INTactivity : STRINGtimestamp : 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
1
'D'
Mon Feb 01 2016 04:00:00.000
2
'A'
Mon Feb 01 2016 01:00:00.000
2
'B'
Mon Feb 01 2016 02:00:00.000
2
'D'
Mon Feb 01 2016 03:00:00.000
2
'E'
Mon Feb 01 2016 04:00:00.000
Output
Result
Column1 : STRING
null
'B'
'C'
null
null
null
null
null



If range specifiers are conflicting, e.g. end activity is before start activity the complete case is mapped to null.


Query
Column1
CALC_CROP_TO_NULL(FIRST_OCCURRENCE [ 'C' ] TO LAST_OCCURRENCE ['B'], "Table1"."activity")
Input
Table1
case : INTactivity : STRINGtimestamp : 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
1
'D'
Mon Feb 01 2016 04:00:00.000
Output
Result
Column1 : STRING
null
null
null
null



Example for FIRST_OCCURRENCE and LAST_OCCURRENCE if activities appear multiple times


Query
Column1
CALC_CROP_TO_NULL(LAST_OCCURRENCE [ 'A' ] TO FIRST_OCCURRENCE ['B'], "Table1"."activity")
Input
Table1
case : INTactivity : STRINGtimestamp : DATE
1
'A'
Mon Feb 01 2016 01:00:00.000
1
'A'
Mon Feb 01 2016 02:00:00.000
1
'B'
Mon Feb 01 2016 03:00:00.000
1
'B'
Mon Feb 01 2016 04:00:00.000
Output
Result
Column1 : STRING
null
'A'
'B'
null



Example how CASE_START and CASE_END can be applied. They can also be mixed LAST_OCCURRENCE and FIRST_OCCURRENCE.


Query
Column1
CALC_CROP_TO_NULL(CASE_START TO CASE_END, "Table1"."activity")
Input
Table1
case : INTactivity : STRINGtimestamp : DATE
1
'A'
Mon Feb 01 2016 01:00:00.000
1
'A'
Mon Feb 01 2016 02:00:00.000
1
'B'
Mon Feb 01 2016 03:00:00.000
1
'B'
Mon Feb 01 2016 04:00:00.000
Output
Result
Column1 : STRING
'A'
'A'
'B'
'B'



Example how through using REMAP_VALUES, CALC_CROP_TO_NULL can be used to crop to the last occurrence of B or C.


Query
Column1
CALC_CROP_TO_NULL(FIRST_OCCURRENCE [ 'A' ] TO LAST_OCCURRENCE ['Z'], REMAP_VALUES("Table1"."activity", ['B', 'Z'], ['C', 'Z']))
Input
Table1
case : INTactivity : STRINGtimestamp : 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
1
'D'
Mon Feb 01 2016 04:00:00.000
Output
Result
Column1 : STRING
'A'
'Z'
'Z'
null



  • No labels