Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

REMAP_VALUES

Applies to: CELONIS 4.5 CELONIS 4.6

Description

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

The first argument is followed by multiple pairs of values (at least one pair) which specify which old value should be replaced by which new value. For REMAP_VALUES the old and new values must be of type STRING and for REMAP_INTS of type  INT. As a last argument a value of type STRING respectively INT. can be specified optionally to replace all not mentioned values from the list of pairs by this value. If such a value is missing, all values not mentioned in the list of pairs stay the same.

If a new value occurs in multiple pairs only the last pair is used.

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

Syntax
REMAP_VALUES ( table.column, [ old_value1, new_value1 ] (, [ old_valueN, new_valueN ] )* [, others_value ] )
REMAP_INTS ( table.column, [ old_value1, new_value1 ] (, [ old_valueN, new_valueN ] )* [, others_value ] )
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] Map String value to String value.

Query

Column1

REMAP_VALUES("Table1"."Column1", [ 'a', 'b' ])

Input

Output

Table1

Column1 : STRING

'a'

Result

Column1 : STRING

'b'

[2] Map same value.

Query

Column1

REMAP_VALUES("Table1"."Column1", [ 'a', 'd' ])

Input

Output

Table1

Column1 : STRING

'a'

'a'

'c'

Result

Column1 : STRING

'd'

'd'

'c'

[3] Map integer value to integer value.

Query

Column1

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

Input

Output

Table1

Column1 : INT

1

2

3

Result

Column1 : INT

1

2

2

[4] Map integer value to null, map null to integer.

Query

Column1

REMAP_INTS("Table1"."Column1", [ 1, NULL ], [ NULL, 3 ])

Input

Output

Table1

Column1 : INT

1

2

null

Result

Column1 : INT

null

2

3

[5] Map string value to null, map null to string value, map others to string.

Query

Column1

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

Input

Output

Table1

Column1 : STRING

'a'

'b'

'NULL'

null

Result

Column1 : STRING

null

'd'

'd'

'c'

[6] Map integer value to integer value, map integer value to null, map others to null.

Query

Column1

REMAP_INTS("Table1"."Column1", [ 1, NULL ], [ 0, 3 ], NULL)

Input

Output

Table1

Column1 : INT

1

2

0

null

Result

Column1 : INT

null

null

3

null

See also: