DSXchange

Posted: **Thu May 30, 2019 12:03 am**

Hello,

I am receiving a file(s) like below, with header as Keys.
So I need to transpose the data to (Key, Value) pairs dynamically. I am able to convert using the Pivot Enterprise stage with Horizontal Pivot, but how to capture the Header and generate the column as shown in desired output?

Code: Select all

File1:
UID|Age|Income|Golf|Outdoor
nabvj42|30|80000|Y|Y
ahfva87987|27|20000|N|N

File2:
UID|MarStatus|Car|Geek|Income
hf7gsb|N|Y|Y|20000
hgj780|Y|Y|N|90000

Desired Output:

Code: Select all

File1:
nabvj42|Age|30
nabvj42|Income|80000
nabvj42|Golf|Y

File2:
hf7gsb|MarStatus|N
hf7gsb|Car|Y
hf7gsb|Geek|Y
hf7gsb|Income|20000

Because my target table has only 3 columns to store the incoming data dynamically.

Code: Select all

UID|Key|Value

Posted: **Thu Jun 13, 2019 1:50 am**

What have you tried?

This is probably more easily accomplished using looping in a Transformer stage, building the output line as you traverse the loop and generating an output row only when LastRecordInGroup(UID) is true.

DSXchange

Horizontal Pivoting with Header

Horizontal Pivoting with Header