DSXchange

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

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.