If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

Parental 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

parent-value

Initial value

parent-value

First encoding - encode columns of the key.

parent-value

Second encoding - encode parents (group of keys).

One parent with two parents with single-column key

pkey1 pkey2

Initial value

pkey1;pkey2

First encoding - encode columns of the key.

pkey1;pkey2

Second encoding - encode parents (group of keys).

Two parents with two-column keys

pkey11 pkey12, pkey21 pkey22

Initial values

pkey11;pkey12 pkey21;pkey22

First encoding - encode columns of the key.

"pkey11;pkey12";"pkey21;pkey22"

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 (;) in the parent value definition separating individual keys).

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

aaa "abc"

initial value

"aaa ""abc"" "

1st encoding - encode keys (there might be multiple), the quote inside (blue) must be doubled with external (yellow) quoting of the value

" ""aaa """"abc"""" "" "

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

p;"11" p"12" p"21" p"22"

initial values

"p;""11"" ;p""12"" ; p""21"" ;p""22"" "

encoding keys

" ""p;""""11"""" "";""p""""12"""" "" ";" "" p""""21"""" "";""p""""22"""" "" "

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?