CALC_CROP_TO_NULL
Description
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. The result is a temporary STRING column of the activity table. It contains activities of the specified range and null otherwise.
Syntax
CALC_CROP_TO_NULL ( 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.
NULL handling
NULL values outside of ranges are ignored and will return NULL again. However, if a row within a range is NULL, then for this row NULL is returned.
[1] CALC_CROP_TO_NULL 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 LAST_OCCURRENCE and FIRST_OCCURRENCE. | ||||||||||||||||||||||||
| ||||||||||||||||||||||||
|
[6] Example how through using REMAP_VALUES, CALC_CROP_TO_NULL can be used to crop to the last occurrence of B or C. | ||||||||||||||||||||||||
| ||||||||||||||||||||||||
|