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
Changing the file from horizontally to vertical
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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
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