Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

CALC_CROP_TO_NULL

Applies to: 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 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, 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 ]

  • 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.

Query

Column1 

CALC_CROP_TO_NULL ( 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

null

Mon Feb 01 2016 04:00:00.000

1

'C'

Mon Feb 01 2016 04:00:00.000

1

'L'

Mon Feb 01 2016 04:00:00.000

1

'C'

Mon Feb 01 2016 05:00:00.000

1

'D'

Mon Feb 01 2016 05:00:00.000

1

null

Mon Feb 01 2016 06:00:00.000

Result

Column1 : STRING

null

'B'

null

'C'

null

'C'

'L'

'C'

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_TO_NULL ( 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 : STRING

null

'B'

'C'

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_TO_NULL ( 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 : STRING

null

null

null

null

[4] Example for FIRST_OCCURRENCE and LAST_OCCURRENCE if activities appear multiple times.

Query

Column1 

         CALC_CROP_TO_NULL ( 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 : STRING

null

'A'

'B'

null

[5] Example how CASE_START and CASE_END can be applied. They can also be mixed LAST_OCCURRENCE and FIRST_OCCURRENCE.

Query

Column1 

         CALC_CROP_TO_NULL ( 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 : STRING

'A'

'A'

'B'

'B'

[6] Example how through using REMAP_VALUES, CALC_CROP_TO_NULL can be used to crop to the last occurrence of B or C.

Query

Column1 

 CALC_CROP_TO_NULL ( 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 : STRING

'A'

'Z'

'Z'

null

See also: