Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4
Description
This operator crops cases to a range of activities. All values outside this range are mapped to null. Values within the range are flagged with 1, in contrast to CALC_CROP which returns the input values.
Syntax
CALC_CROP ( begin_range_specifier TO end_range_specifier, table.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_name']: First occurrence of the specified activity type.
- LAST_OCCURRENCE['activity_name']: 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(FIRST_OCCURRENCE [ 'B' ] TO LAST_OCCURRENCE ['C'], "Table1"."activity")
Input
Table1
Output
Result
| Column1 : INT |
|---|
null |
1 |
1 |
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(FIRST_OCCURRENCE [ 'C' ] TO LAST_OCCURRENCE ['B'], "Table1"."activity")
Output
Result
| Column1 : INT |
|---|
null |
null |
null |
null |
Example for FIRST_OCCURRENCE and LAST_OCCURRENCE if activities appear multiple times
Query
Column1
CALC_CROP(LAST_OCCURRENCE [ 'A' ] TO FIRST_OCCURRENCE ['B'], "Table1"."activity")
Output
Result
| Column1 : INT |
|---|
null |
1 |
1 |
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(CASE_START TO CASE_END, "Table1"."activity")
Output
Result
| Column1 : INT |
|---|
1 |
1 |
1 |
1 |
Example how through using REMAP_VALUES, CALC_CROP can be used to crop to the last occurrence of B or C.
Query
Column1
CALC_CROP(FIRST_OCCURRENCE [ 'A' ] TO LAST_OCCURRENCE ['Z'], REMAP_VALUES("Table1"."activity", ['B', 'Z'], ['C', 'Z']))
Output
Result
| Column1 : INT |
|---|
1 |
1 |
1 |
null |