Record Formation

[pavan_test]

Hi All,

Can someone please tell me how do I accomplish the following in datastage 8.1 version.

incoming record:

colA COLB ColD
1 2 3

expected output:

COL E:
1
2
3


incoming record:

colA COLB ColD
1 1 1

expected output:

COL E:
1

incoming record:

colA COLB ColD
1 1 2

expected output:

COL E:
1
2

incoming record:

colA COLB ColD
2 1 1

expected output:

COL E:
2
1

incoming record:

colA COLB ColD
1 2 1

expected output:

COL E:

1
2

Thanks
Pavan

[chulett]

So... what are your thoughts on this? What have you tried so far?

To me it looks like a normal horizontal pivot with an extra check for repeated values that should be constrained out of the flow. All except for the last one where it makes it look like you need to sort the output before the duplicate check is done. But that would mess up the next-to-last example.

Are your examples 100% correct?

[pavan_test]

I am new to datastage. I did not write any code for horizontal pivot in the past. Looking for thoughts.

[ArndW]

It looks like your expected output could be achieved with 2 stages:

1. As Craig already mentioned, do a simple horizontal pivot
2. Perform a unique sort on your resultant data on the output Col E

If you start with the horizontal pivot stage and get that to function as you expect then you can decide how to approach the second part where you filter out the duplicates. If order isn't important then the unique sort will solve your issues, if not then we'll have to use a different approach.