DSXchange

Hi all.

I have a file where the data is listed horizontally. Each row has a composite key, i.e., Key1 and key2 and the columns of data. The keys can be in the file 1 to 5 times. How can I list the data vertically one key followed by the repeated data?

Thanks in advance for any help

Key1 Key2 Fld1 Fld2 Fld3
001 00A 123 456 789
002 00B xxx yyy zzz
002 00B uuu vvv www
003 00Q ddd eee fff


001 00A 123 456 789
002 00B xxx yyy zzz uuu vvv www
003 00Q ddd eee fff

The technique you need is a variation on one that is called "vertical pivot". Search the forums for techniques. In parallel jobs the most usual technique is to assemble each unique key's record in stage variables in a Transformer stage. Presumably your non-key repeated data will all be in one field.

Thanks ray. I started with viewtopic.php?t=118685 and ended up with the following:

svColId =
If Link_In.keyChange Then 1 Else svColId + 1

svConCat =
If Link_In.keyChange = 1 Then Link_In.Fld1 : "," : Link_In.Fld2 : "," : Link_In.Fld3 Else
svConCat : "|" : Link_In.Fld1 : "," : Link_In.Fld2 : "," : Link_In.Fld3

Then I:
- Mapped key1, key2, svColId, svConCat to a Sort Stage and sorting on the first three. I'm guessing I could have done without this sort. But just incase.
- Then used the Remove Duplicates Stage on key1, key2 retaining the last one