One input record to multiple output records
Moderators: chulett, rschirm, roy
One input record to multiple output records
Hi, I'm hoping someone can help me. We have a server job that has a performance problem and I am hoping to correct it.
A transformer reads in a file and for each record in that file we write out to 10 different files, then we merge them back into a single file using the link collector. We don't really want to save the 10 different files, but the link collector forces us to, since it doesn't accept active-to-active inputs.
So, for example, say our file contains the alphabet, one letter per row:
Letter
a
b
c
d
e
:
:
We want to end up with a file that reads:
Letter   Number
a 1
a 2
a 3
a 4
a 5
a 6
a 7
a 8
a 9
a 10
b 1
b 2
b 3
b 4
b 5
:
:
Does anyone have a suggestion on how to do this more effciently? Thank you!
A transformer reads in a file and for each record in that file we write out to 10 different files, then we merge them back into a single file using the link collector. We don't really want to save the 10 different files, but the link collector forces us to, since it doesn't accept active-to-active inputs.
So, for example, say our file contains the alphabet, one letter per row:
Letter
a
b
c
d
e
:
:
We want to end up with a file that reads:
Letter   Number
a 1
a 2
a 3
a 4
a 5
a 6
a 7
a 8
a 9
a 10
b 1
b 2
b 3
b 4
b 5
:
:
Does anyone have a suggestion on how to do this more effciently? Thank you!
Re: One input record to multiple output records
If it's just the each line of a file with a number appended to it: generate 10 lines in the transformer (including the newlines) per inputline and output that.
Ogmios
Ogmios
To generate the 10 lines though, don't you need to have 10 output files?
One with:
a 1
b 1
c 1
d 1
:
:
and another with:
a 2
b 2
c 2
d 2
:
:
Up to 10. That's what we have, then we merge the 10 files back together to 1 file using the link collecter.
The problem is we end up creating and saving 10 files that we don't really want and we end up with a performance problem.
So, is there a different way to generate 10 rows using the transformer that will all be within the same ouput file?
One with:
a 1
b 1
c 1
d 1
:
:
and another with:
a 2
b 2
c 2
d 2
:
:
Up to 10. That's what we have, then we merge the 10 files back together to 1 file using the link collecter.
The problem is we end up creating and saving 10 files that we don't really want and we end up with a performance problem.
So, is there a different way to generate 10 rows using the transformer that will all be within the same ouput file?
-
- Participant
- Posts: 145
- Joined: Fri May 02, 2003 9:59 am
- Location: Seattle, Washington. USA
Re: One input record to multiple output records
I am assuming that you really want to represent a single line 10 times and that the link collector is the only way that you figured out how to do it. What I would do is use a transformer to take the and replicate the source column into 10 columns. I would then feed that transformer into a pivot stage to take the columns and make them rows.
So using your example.
The feed would look like this into the transformer:
So using your example.
The feed would look like this into the transformer:
Code: Select all
Letter
a
b
Out of the Transformer:
Letter1 Number1 Letter2 Number2 ... Letter10 Number10
a 1 a 2 a 10
b 1 b 2 b 10
Out of the Pivot:
Letter Number
a 1
a 2
a 3
a 4
a 5
a 6
a 7
a 8
a 9
a 10
b 1
b 2
b 3
b 4
b 5
:
:
Shawn Ramsey
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
Read the file with a table def of just one column (the whole row sits in the column) like varchar 4000, into column name "A".
In the transformer, concatenate the row 10 times with the incrementing line number and the row terminator for the target OS (looks like you're on NT).
Example for the derivation on the output in the transformer, assuming your resultant file is comma seperated:
A : ",1" : char(013) : char(010) : A : ",2" : char(013) : char(010) :A : ",3" : char(013) : char(010) : ... : A : ",10" : char(013) : char(010)
Output to a seq file with no line terminator (000), and no column delimeters (000).
View the file in notepad or whatever and you'll have a file that has 10 rows for every one row written from the transformer.
Send me a private email if you don't understand this ( I had to write it in a hurry).
In the transformer, concatenate the row 10 times with the incrementing line number and the row terminator for the target OS (looks like you're on NT).
Example for the derivation on the output in the transformer, assuming your resultant file is comma seperated:
A : ",1" : char(013) : char(010) : A : ",2" : char(013) : char(010) :A : ",3" : char(013) : char(010) : ... : A : ",10" : char(013) : char(010)
Output to a seq file with no line terminator (000), and no column delimeters (000).
View the file in notepad or whatever and you'll have a file that has 10 rows for every one row written from the transformer.
Send me a private email if you don't understand this ( I had to write it in a hurry).
-
- Participant
- Posts: 145
- Joined: Fri May 02, 2003 9:59 am
- Location: Seattle, Washington. USA
It is under the Processing Palette and looks like a box with an arrow pointing up and another pointing left. The derivation column is a little weird and I had to go to the .pdf several times to figure it out. It basically takes a comma delimited list of source columns.jlockwo wrote:A Pivot Stage? That sounds like it would work, but I've never heard of such a thing. Could you explain where you find a pivot stage in datastage? I mean under what grouping on the palette?
Shawn Ramsey
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams