One Star

Create multiple output rows for one input row

Hello,
i'm new in working with Talend Open Studio (TOS opensource edition).
In our datamigration project we have the following requirement in data transformation.
we have a input table with the folling structure:
orderid | descritpion | responsible| executor | ...
--------------------------------------------------------
4711 | bla1 | Frank | Peter
4712 | blabla | John |
4713 | sdjfklsj | James | Bart
what i need as an output must look like this:
id | orderid | function | name
-------------------------------------
0815 | 4711 | responsible | Frank
0816 | 4711 | executor | Peter
0817 | 4712 | responsible | John
0818 | 4713 | responsible | James
0819 | 4713 | executor | Bart
So i have to create a output row for a column content and the output row has a foreign key (orderid) to the input table. The id in the output table can be generated. With our own ETL Tool we can fit these requirements and i am sure TOS can also.
But how?
11 REPLIES
One Star Lie
One Star

Re: Create multiple output rows for one input row

Hi
You can divide by using a tMap in two flows. (one with responsible & one with executor) then output both in temp files. Then you load the temp files and "unite" flows.
You can also unite your flows in a single temp file.


Regards,
Lie
One Star

Re: Create multiple output rows for one input row

Thanks Lie, it work's !
One Star

Re: Create multiple output rows for one input row

how can I perform reverse process where I have multiple rows for particular id. i want all record to one id should be in one row.I dont want to store in a file system
One Star Lie
One Star

Re: Create multiple output rows for one input row

Shaky :
You can use a tMap.
row1
id | column1
row2
id | column2
output
id | column1 | column2

Map row1.id to row2.id and output.id
Then map row1.column1 to output.column1 and row2.column2 to output.column2.
The reverse process is easy Smiley Wink
One Star

Re: Create multiple output rows for one input row

Did not understood how to do using tMap. Can you explain with screenshot? it would be gr8 help.
One Star Lie
One Star

Re: Create multiple output rows for one input row

rowExecutor :
1|executor|Millard
2|executor|Harry
3|executor|George
4|executor|James
5|executor|Harry
rowManager :
1|manager|John
2|manager|Rutherford
3|manager|Thomas
4|manager|John
5|manager|Grover

Output :
1|Millard|John
2|Harry|Rutherford
3|George|Thomas
4|James|John
5|Harry|Grover

Hope my exemple is clear enough.
One Star

Re: Create multiple output rows for one input row

i have input like
id:1
name:ss
age:15
id:2
name:as
age:14
How can I do this in one row for each record
One Star Lie
One Star

Re: Create multiple output rows for one input row

See my example below. This is the way I see it.
Use a tFileInputDelimited to separate your keys and values (set the separator to ":").
Set ids yor each of your rows as in my example below.
Then you could use my example above to unite your rows.

Hope it helps.
Seven Stars

Re: Create multiple output rows for one input row

You can divide by using a tMap in two flows. (one with responsible & one with executor) then output both in temp files. Then you load the temp files and "unite" flows.

Rather than sending the two tMap outputs to a file and then tUniting them, you can unite them directly in the tMap: when creating the second output within tMap just select the "Create join table from" option rather than the default "New output" option. There will then be only one flow out of the tMap but it will include the rows from both of the output tables within tMap.
Seven Stars

Re: Create multiple output rows for one input row

There is also from v4.2.0, the tSplitRow component...
One Star

Re: Create multiple output rows for one input row

You can divide by using a tMap in two flows. (one with responsible & one with executor) then output both in temp files. Then you load the temp files and "unite" flows.

Rather than sending the two tMap outputs to a file and then tUniting them, you can unite them directly in the tMap: when creating the second output within tMap just select the "Create join table from" option rather than the default "New output" option.  There will then be only one flow out of the tMap but it will include the rows from both of the output tables within tMap.
Thanks for the answer... this worked for meSmiley Happy