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

Examples


[1] Map String value to String value

Query
Column1
REMAP_VALUES("Table1"."Column1", [ 'a', 'b' ])
Input
Table1
Column1 : STRING
'a'
Output
Result
Column1 : STRING
'b'



[2] Map same value

Query
Column1
REMAP_VALUES("Table1"."Column1", [ 'a', 'd' ])
Input
Table1
Column1 : STRING
'a'
'a'
'c'
Output
Result
Column1 : STRING
'd'
'd'
'c'



[3] Map Integer value to Integer value

Query
Column1
REMAP_INTS("Table1"."Column1", [ 3, 2 ])
Input
Table1
Column1 : INT
1
2
3
Output
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
Table1
Column1 : INT
1
2
null
Output
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
Table1
Column1 : STRING
'a'
'b'
'NULL'
null
Output
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
Table1
Column1 : INT
1
2
0
null
Output
Result
Column1 : INT
null
null
3
null


See also:

  • No labels