Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

REMAP_VALUES

Applies to: CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

Description

REMAP_VALUES / REMAP_INTS allows you to map values of a column. REMAP_VALUES takes a column of type STRING and REMAP_INTS takes a column of type INT.

Syntax
REMAP_VALUES ( table.column, [ old_value1, new_value1 ] , ... [, others_value ] )
REMAP_INTS ( table.column, [ old_value1, new_value1 ] , ... [, others_value ] )

The first parameter of REMAP_VALUES is an input column of type STRING that provides the values that should be remapped. The second parameter is a list of one or more pairs of string values that describe the mapping. Each occurrence of the first value of the pair will be remapped to the second value of the pair. If a value is remapped in multiple pairs, only the last pair is used. Finally, the operator accepts an optional string value that will replace all values that are not remapped within the mapping. If this optional default replacement value is missing, all values not considered in the mapping will remain unchanged.

As the REMAP_VALUES operator is only applicable to columns of type string, REMAP_INTS provides a similar functionality for columns of type INT.

You can achieve the same result with a CASE WHEN statement but REMAP_VALUES / REMAP_INTS is faster and the syntax is more compact.

REMAP_VALUES is often used to ignore certain activities in process functions by setting those activities to NULL, for example in the SOURCE and TARGET filter column.

NULL handling

If the input value is NULL and it is not mapped to any other value, REMAP_VALUES / REMAP_INTS will return NULL again.

Examples

[1] Simple example of the REMAP_VALUES operator. It takes the activity column of the activity table as input and maps 'B' and 'C' to NULL. As the optional replacement value is not defined, all the other values ('A' and 'D') remain the same.

Query

Column1

REMAP_VALUES ( "ACTIVITIES"."ACTIVITY" , [ 'B' , NULL ] , [ 'C' , NULL ] )

Input

Output

ACTIVITIES

CASE_ID : INT

ACTIVITY : STRING

TIMESTAMP : DATE

1

'A'

Tue Jan 01 2019 13:00:00.000

1

'B'

Tue Jan 01 2019 13:01:00.000

1

'C'

Tue Jan 01 2019 13:07:00.000

1

'D'

Tue Jan 01 2019 13:09:00.000

2

'B'

Tue Jan 01 2019 13:03:00.000

2

'D'

Tue Jan 01 2019 13:06:00.000

Result

Column1 : STRING

'A'

null

null

'D'

null

'D'

[2] Simple example of REMAP_INTS, where all occurrences of integer 3 are mapped to 2.

Query

Column1

REMAP_INTS ( "Table1"."Column1" , [ 3 , 2 ] )

Input

Output

Table1

Column1 : INT

1

3

2

3

Result

Column1 : INT

1

2

2

2

[3] In this example, 'a' is remapped to NULL, and NULL values are mapped to 'c'. Note that the mappings are not applied incrementally; only the last matching mapping for each input row is applied.

Query

Column1

REMAP_VALUES ( "Table1"."Column1" , [ 'a' , 'x' ] , [ 'a' , NULL ] , [ NULL , 'c' ] )

Input

Output

Table1

Column1 : STRING

'a'

'b'

'NULL'

null

Result

Column1 : STRING

null

'b'

'NULL'

'c'

[4] In this example, REMAP_VALUES is used to set all activities except 'A' and 'B' to NULL. 'A' and 'B' are mapped to their current value (i.e. they remain unchanged), and all other values are mapped to NULL using the optional default replacement parameter.

Query

Column1

REMAP_VALUES ( "ACTIVITIES"."ACTIVITY" , [ 'A' , 'A' ] , [ 'B' , 'B' ] , NULL )

Input

Output

ACTIVITIES

CASE_ID : INT

ACTIVITY : STRING

TIMESTAMP : DATE

1

'A'

Tue Jan 01 2019 13:00:00.000

1

'B'

Tue Jan 01 2019 13:01:00.000

1

'C'

Tue Jan 01 2019 13:07:00.000

1

'D'

Tue Jan 01 2019 13:09:00.000

2

'B'

Tue Jan 01 2019 13:03:00.000

2

'D'

Tue Jan 01 2019 13:06:00.000

Result

Column1 : STRING

'A'

'B'

null

null

'B'

null

See also: