Lead your team forward
OCT 24 / 9AM ET Register nowParental Values Encoding in RDM MN References
Parental keys need to be encoded into the children’s binding column to preserve its hierarchical nature. A child can have multiple parents and each parent can be composed of multiple values (columns) in case of a compound key.
To ensure proper encoding and decoding of the value, a two-level encoding has to be used where in the first phase we encode individual key columns, and in the second phase we encode or group individual parents.
The CSV encoding is performed with the following setup:
-
string qualifier = "
-
string qualifier escape = "
-
field separator = ;
-
use string qualifier for all values = false
This means that only values which contain a single double quotation mark ("
) or semicolon (;
) are escaped.
This setup is hardcoded in RDM and cannot be changed as changing these values would require recalculating all parent-binding values in MNChild tables.
Examples
One parent with single-column key
|
Initial value |
|
First encoding - encode columns of the key. |
|
Second encoding - encode parents (group of keys). |
One parent with two parents with single-column key
|
Initial value |
|
First encoding - encode columns of the key. |
|
Second encoding - encode parents (group of keys). |
Two parents with two-column keys
|
Initial values |
|
First encoding - encode columns of the key. |
|
Second encoding - encode parents. As each parent has two keys, we must group parents together using quotes (technically, it is because there is a semicolon ( |
Escaping quotes and separators
If a double quotation mark ("
) or a semicolon (;
) is contained in the key value, the encoding gets more complicated as these characters enforce key value quoting and the double quotation mark requires escaping to distinguish the value character from the quoting character.
In the following examples, we use the same color for the related quote characters to make them easier to spot.
One parent with single-column key
|
initial value |
" |
1st encoding - encode keys (there might be multiple), the quote inside (blue) must be doubled with external (yellow) quoting of the value |
" "" |
2nd encoding - encode parents (there might be multiple) - each inner quote (from the previous step) must be doubled again (blue and yellow quotes) and the resulting value must be quoted again (red quotes) |
Two parents with two-column keys
|
initial values |
" |
encoding keys |
" "" |
encoding parents |
This additional quoting and doubling is needed because:
-
Without outer quoting, we would have issues with separators (
;
). -
Without inner quoting (doubling inner quotes), we would have issues with proper quotes decoding.
Decoding stored values
If you want to decode encoded values back to the parental key values, you must use the CSV decoding algorithm with the same setup as when encoding:
-
string qualifier = "
-
string qualifier escape = "
-
field separator = ;
Example
This example can be processed using the Text File Reader step in ONE Desktop.
input |
"""key """"1"""""";""key """"2""""""";"""key """"3"""""";""key """"4""""""" |
output |
"key ""1""";"key ""2""";"key ""3""";"key ""4""" |
You get CSV encoded values of individual keys of individual parents. To get unencoded values of the keys, perform CSV decoding on those values once again.
input |
"key ""1""";"key ""2""";"key ""3""";"key ""4""" |
output |
key "1"; key"2"; key "3"; key "4" |
Was this page useful?