Normalize dataset as key-value pairs transformation

When to use this transformation

This transformation converts columns to key-value pairs, where the key is a column name and the value is a column value.

Parameters

• Columns to Convert: a comma-separated list of columns to convert to key-value pairs. For example: firstName, lastName, ssn, age. ssn and age are configured as columns to convert. Result: firstName, lastName, key, value, where keys are ssn and age, and values are values for these columns.
• Columns to Not Convert: works as an opposite to Columns to Convert. Basically, everything EXCEPT these columns will be converted to key-value pairs. If the value of the field Columns to Convert is not empty, the value of the field Columns to Not Convertwill be ignored.
• Key Column Name: name of the actual column which will be used as a Key.
• Value Column Name: name of the actual column which will be used as a Value.

Example with columns to convert

With Columns to Convert set to type,name, Key Column Name set to key, and Value Column Name set to Value:

Before

id,type,name
1,first,joe
2,last,jude
3,last,who

After

id,key,value
1,type,first
1,name,joe
2,type,last
2,name,jude
3,type,last
3,name,who

Example with columns to not convert

With Columns to Not Convert set to id, Key Column Name set to key, and Value Column Name set to Value:

Before

id,type,name
1,first,joe
2,last,jude
3,last,who

After

id,key,value
1,type,first
1,name,joe
2,type,last
2,name,jude
3,type,last
3,name,who

Process

To configure this transformation go to Transformation/ MAPPING/ Complex Transformations/ Convert columns to the key-value pairs.