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
Retrieval of Pivotal Column values
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.