Skip to main content

Celonis Product Documentation

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.

Query

Column1 

         CALC_CROP ( FIRST_OCCURRENCE [ 'B' ] TO LAST_OCCURRENCE [ 'C' ] , "Table1"."activity" )

Input

Output

Table1

case : int

activity : string

timestamp : date

1

null

Mon Feb 01 2016 01:00:00.000

1

'B'

Mon Feb 01 2016 02:00:00.000

1

null

Mon Feb 01 2016 02:05:00.000

1

'C'

Mon Feb 01 2016 03:00:00.000

1

'C'

Mon Feb 01 2016 04:00:00.000

1

null

Mon Feb 01 2016 04:00:00.000

1

'L'

Mon Feb 01 2016 04:00:00.000

1

'C'

Mon Feb 01 2016 04:00:00.000

1

'D'

Mon Feb 01 2016 04:00:00.000

1

null

Mon Feb 01 2016 04:00:00.000

Result

Column1 : int

null

1

1

1

1

1

1

1

null

null

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.

Query

Column1 

         CALC_CROP ( FIRST_OCCURRENCE [ 'B' ] TO LAST_OCCURRENCE [ 'C' ] , "Table1"."activity" )

Input

Output

Table1

case : int

activity : string

timestamp : 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

Result

Column1 : int

null

1

1

null

null

null

null

null

[3]

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" )

Input

Output

Table1

case : int

activity : string

timestamp : 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

Result

Column1

null

null

null

null

[4]

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" )

Input

Output

Table1

case : int

activity : string

timestamp : 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

Result

Column1 : int

null

1

1

null

[5]

Example how CASE_START and CASE_END can be applied. They can also be mixed with LAST_OCCURRENCE and FIRST_OCCURRENCE.

Query

Column1 

         CALC_CROP ( CASE_START TO CASE_END , "Table1"."activity" )

Input

Output

Table1

case : int

activity : string

timestamp : 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

Result

Column1 : int

1

1

1

1

[6]

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' ] ) )

Input

Output

Table1

case : int

activity : string

timestamp : 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

Result

Column1 : int

1

1

1

null

[7]

CALC_CROP query using non-existing activity in FIRST_OCCURRENCE: Empty result and warning.

Query

Column1 

         CALC_CROP ( FIRST_OCCURRENCE [ 'X' ] TO LAST_OCCURRENCE [ 'C' ] , "Table1"."activity" )

Input

Output

Table1

case : int

activity : string

timestamp : 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

Result

Column1

null

null

null

null

null

null

null

null

Warning

CALC_CROP: Source activity [X] could not be found in activity column ["Table1"."activity"].

See also: