CALC_CROP
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_TO_NULL which returns the input values. The result is an INT column that is activity-based and returns 1 for the activities inside the specified range and null otherwise.
Syntax
CALC_CROP ( begin_range_specifier TO end_range_specifier, activity_table.string_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 ]
activity_table.string_column: A string column of an activity table. Usually, the activity column of an activity table is used.
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.
If the activity name used in FIRST_OCCURRENCE or LAST_OCCURRENCE does not exist, then a warning is displayed. In this scenario all values are mapped to null.
NULL handling
NULL values outside of ranges are ignored and will return NULL again. However, if there are NULL values within a range, they are also flagged with 1.
[1] CALC_CROP query with null values within the range as well as outside of the range. | ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
|
Examples
[2] 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. | ||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||
|
[3] If range specifiers are conflicting, e.g. end activity is before start activity the complete case is mapped to null. | ||||||||||||||||||||||||
| ||||||||||||||||||||||||
|
[4] Example for FIRST_OCCURRENCE and LAST_OCCURRENCE if activities appear multiple times. | ||||||||||||||||||||||||
| ||||||||||||||||||||||||
|
[5] Example how CASE_START and CASE_END can be applied. They can also be mixed with LAST_OCCURRENCE and FIRST_OCCURRENCE. | ||||||||||||||||||||||||
| ||||||||||||||||||||||||
|
[6] Example how through using REMAP_VALUES, CALC_CROP can be used to crop to the last occurrence of B or C. | ||||||||||||||||||||||||
| ||||||||||||||||||||||||
|
[7] CALC_CROP query using non-existing activity in FIRST_OCCURRENCE: Empty result and warning. | ||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||
|