Versions Compared

Old Version 1

changes.mady.by.user Max Kohl

Saved on

compared with

New Version Current

changes.mady.by.user David Becher

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Applies to:  
Status
colourGreen
titleCelonis 4.2
 
Status
colourGreen
titleCelonis 4.3
 
Status
colourGreen
titleCelonis 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


Code Block
languagetext
themeDJango
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.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CALC_CROP_TO_NULL(FIRST_OCCURRENCE [ 'B' ] TO LAST_OCCURRENCE ['C'], "Table1"."activity")




Panel
titleInput
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




Panel
titleOutput
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.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CALC_CROP_TO_NULL(FIRST_OCCURRENCE [ 'C' ] TO LAST_OCCURRENCE ['B'], "Table1"."activity")




Panel
titleInput
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




Panel
titleOutput
Result


Column1 : STRING
'
null
'
'
null
'
'
null
'
'
null
'





Example for FIRST_OCCURRENCE and LAST_OCCURRENCE if activities appear multiple times



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CALC_CROP_TO_NULL(LAST_OCCURRENCE [ 'A' ] TO FIRST_OCCURRENCE ['B'], "Table1"."activity")




Panel
titleInput
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




Panel
titleOutput
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.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CALC_CROP_TO_NULL(CASE_START TO CASE_END, "Table1"."activity")




Panel
titleInput
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




Panel
titleOutput
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.



Panel
titleQuery
Column1


Code Block
languagetext
themeDJango
CALC_CROP_TO_NULL(FIRST_OCCURRENCE [ 'A' ] TO LAST_OCCURRENCE ['Z'], REMAP_VALUES("Table1"."activity", ['B', 'Z'], ['C', 'Z']))




Panel
titleInput
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




Panel
titleOutput
Result


Column1 : STRING
'A'
'Z'
'Z'
'
null
'