Applies to: CELONIS 4.2 CELONIS 4.3 CELONIS 4.4
column : Any string column which can be joined to the activity table
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>]
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 caseExamples
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
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")
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")
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")
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']))
Output
Result
Column1 : STRING |
---|
'A' |
'Z' |
'Z' |
null |