Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

CALC_CROP

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

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.

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.

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. (Since: CELONIS 4.6)

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

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

null

null

null

null

null

null

null

null

See also: