Horizontal Pivoting with Header

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
karumudi7
Participant
Posts: 20
Joined: Sun Mar 11, 2012 3:30 am
Location: Detroit
Contact:

Horizontal Pivoting with Header

Post by karumudi7 »

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
DataStage 11.3 on AIX 7.1 with DB2.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply