Retrieval of Pivotal Column values

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
chakkara
Participant
Posts: 5
Joined: Thu Jun 28, 2018 7:05 pm

Retrieval of Pivotal Column values

Post by chakkara »

I got a requirement to convert columns into rows and then based on column names set default values corresponding to key. Tried using vertical pivot option in pivot enterprise but not able to compare the name values to set default value

Below is the example :

Input :

Key Name
1 ABC
1 DEF
1 GHI
1 JKL
2 ABC
2 DEF
2 JKL
3 DEF
3 GHI
3 JKL
4 ABC
4 DEF

Expected Output :

If a particular key contains both ABC and DEF then set value as 'G'
if key contains only DEF,GHI and JKL then set value as ''C'
If key contains ABC,DEF and JKL then set value as 'E'
If key contains ABC,DEF,GHI and JKL then set value as 'L'

O/p:

Key Value
1 L
2 E
3 C
4 G
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I think this problem would be suited to looping in a Transformer stage, using the utility functions SaveInputRecord() and GetSavedInputRecord() to manipulate the stack of records for the current key, and loop variables to detect the particular values' existence. Use LastRecordInGroup() function to determine when to transfer the result to the output link.
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