Split a row into multiple rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 25
- Joined: Wed Oct 13, 2004 1:11 am
Split a row into multiple rows
Hi,
This is my first post, quite a happening forum I must say. My question is how can we split a row with more than 50 columns which has 6 colums to be split into 6 rows. Whats the best strategy for implementing this. I know it can be done in a multitude of ways.
Thanks
Sam
This is my first post, quite a happening forum I must say. My question is how can we split a row with more than 50 columns which has 6 colums to be split into 6 rows. Whats the best strategy for implementing this. I know it can be done in a multitude of ways.
Thanks
Sam
Welcome to our happening forum, Sam!
You've got a couple of primary choices to do what you need to do. Some of these answers may need to be tempered by the exact nature of your output rows - are they all of the same format / metadata or will they have different metadata? Your target - database, hash or sequential file - can make a difference as well.
Because you are on version 7.x you have access to the Row Splitter stage, which can be leveraged to create mutliple rows from a single row. You could then (possibly) use the Row Merger stage to put them back together again.
The 'traditional' answer is to take advantage of the record seperator character(s) for your platform and write out to a sequential file. The process is covered in a posting in the FAQ forum, here. In a nutshell, you write out one record with the various 'sub-records' delimited by CR/LF pairs for Windows (or just LFs for UNIX) and when the resulting file is opened afterwards, you magically have more rows that you started with.
So, there's a couple of different ways to investigate. On the off chance you're not aware of this, the pdf documentation for all of the plugin stages (Row Splitter/Merger) are installed automatically in the 'Docs' directory under your Client install directory.
![Laughing :lol:](./images/smilies/icon_lol.gif)
You've got a couple of primary choices to do what you need to do. Some of these answers may need to be tempered by the exact nature of your output rows - are they all of the same format / metadata or will they have different metadata? Your target - database, hash or sequential file - can make a difference as well.
Because you are on version 7.x you have access to the Row Splitter stage, which can be leveraged to create mutliple rows from a single row. You could then (possibly) use the Row Merger stage to put them back together again.
The 'traditional' answer is to take advantage of the record seperator character(s) for your platform and write out to a sequential file. The process is covered in a posting in the FAQ forum, here. In a nutshell, you write out one record with the various 'sub-records' delimited by CR/LF pairs for Windows (or just LFs for UNIX) and when the resulting file is opened afterwards, you magically have more rows that you started with.
![Wink :wink:](./images/smilies/icon_wink.gif)
So, there's a couple of different ways to investigate. On the off chance you're not aware of this, the pdf documentation for all of the plugin stages (Row Splitter/Merger) are installed automatically in the 'Docs' directory under your Client install directory.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 25
- Joined: Wed Oct 13, 2004 1:11 am
Thanks Craig I dont have the splitter.pdf, I will try to find out more about the row splitter and merger stage. Can I have 6 hash files which would have all the coulmns and one of the 6 columns to be split into separate row then use funnel stage if its a parallel environment ?, how else we could do in server jobs other than row splitter and merger. I want all the coloumns and one of the 6 coloumns in the resultant data. A little more explanantion would be really helpful.
Sam.
Sam.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
What you may need is a pivot via the pivot stage. Read the pivot stage documentation to see if it matches. I don't think the rowsplitter can turn a single row into multiple rows.
You're description is a bit vague, you have about 50 columns, you are trying to duplicate 44 of those columns 6 times over with the remaining 6 columns spread between those duplicates. Is this correct?
You're description is a bit vague, you have about 50 columns, you are trying to duplicate 44 of those columns 6 times over with the remaining 6 columns spread between those duplicates. Is this correct?
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Some random points...
You may not have the docs for the Row Splitter/Merger stages, as some of the early disks left them out. Vincent and I got them uploaded to Ascential's Developer Net some time ago and they are here if you have access to their file library.
Not really knowing the format or either your input or output records, I thought the Splitter might help. Still might help, it's hard to say.
When you mention the Funnel stage, you are venturing into the Parallel universe. There's a whole 'nother forum for that and a whole 'nother set of gurus over there, so it would probably be best to pursue that over there once this has played out.
Vince is right, you may be able to use the Pivot stage. Check the docs and see if it makes sense given your need... which is still a little hard to understand. It might help us all if you could clarify what it is you are trying to do. Perhaps a example of your input data and what the end result should look like?
You may not have the docs for the Row Splitter/Merger stages, as some of the early disks left them out. Vincent and I got them uploaded to Ascential's Developer Net some time ago and they are here if you have access to their file library.
Not really knowing the format or either your input or output records, I thought the Splitter might help. Still might help, it's hard to say.
![Confused :?](./images/smilies/icon_confused.gif)
When you mention the Funnel stage, you are venturing into the Parallel universe. There's a whole 'nother forum for that and a whole 'nother set of gurus over there, so it would probably be best to pursue that over there once this has played out.
Vince is right, you may be able to use the Pivot stage. Check the docs and see if it makes sense given your need... which is still a little hard to understand. It might help us all if you could clarify what it is you are trying to do. Perhaps a example of your input data and what the end result should look like?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 25
- Joined: Wed Oct 13, 2004 1:11 am
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
In that case the pivot stage should be able to handle this.
The other thing you could do is have 6 outputs from your transformer to 6 different files and concatenate them all together.
The other thing you could do is have 6 outputs from your transformer to 6 different files and concatenate them all together.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Then, as Vincent noted, you can use the Pivot stage. Bring all of your columns in on the Input side, and then for the Output side list the first 44 columns. In the 45th column, list your 'remaining 6 columns' comma seperated.coolkhan08 wrote:I want to duplicate the 44 columns 6 times with each row containing one of the remaining 6 columns.
You may need to mark the first 44 as 'Keys' or as 'Grouped', I don't recall. The docs make it seem like it's not necessary, that it is driven by 'single derivation' versus 'multiple derivation', but I think it may be. Try it first without.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 25
- Joined: Wed Oct 13, 2004 1:11 am