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
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:
Column1
REMAP_VALUES ( "ACTIVITIES"."ACTIVITY" , [ 'B' , NULL ] , [ 'C' , NULL ] )
Result
| Column1 : STRING |
|---|
| 'A' |
| null |
| null |
| 'D' |
| null |
| 'D' |
Column1
REMAP_VALUES ( "Table1"."Column1" , [ 'a' , 'x' ] , [ 'a' , NULL ] , [ NULL , 'c' ] )
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:
Column1
REMAP_VALUES ( "ACTIVITIES"."ACTIVITY" , [ 'A' , 'A' ] , [ 'B' , 'B' ] , NULL )
Result
| Column1 : STRING |
|---|
| 'A' |
| 'B' |
| null |
| null |
| 'B' |
| null |