topic Re: Transposing data and merging two files in Talend Studio

Transposing data and merging two files

Anonymous — Thu, 02 Mar 2017 23:09:34 GMT

My requirement is to transpose rows to columns. I am using tPivotToColumnsDelimited. But this currently allows aggregation of one column. I have to transpose the row data of three columns, where one column is the common pivot column for the other two columns.
So I have used this component twice, in the hope to merge the two resultant files (CSV) into one output later.
Ideally I could use tMap component to merge the two CSV files. But I do not have a predefine schema, as the data in the files is not static. So I am unable to define a schema for input source to tMap.
Can someone please suggest a way forward? I’ll be grateful for your help.
Regards,
Vishakha

Re: Transposing data and merging two files

Anonymous — Fri, 03 Mar 2017 03:40:26 GMT

Hi Vishakha,
Could you please elaborate your case with an example with input and expected output values?
Best regards
Sabrina

Re: Transposing data and merging two files

Anonymous — Fri, 03 Mar 2017 10:21:46 GMT

Hello Sabrina.
Thanks for getting back to me.
We use the community version of TOS.

My requirement is to transpose the following input:

ID TEXT     COMMENT
1   Text A1 Comment A1
2   Text B1 Comment B1
3   Text C1 Comment C1
4   Text D1 Comment D1
5   Text E1 Comment E1

To the following output:

ID1 Text   ID 1 Comment    ID 2 Text    ID 2 Comment     ID 3 Text     ID 3 Comment    ID 4 Text    ID 4 Comment    ......
Text A1     Comment A1         Text B1       Comment B1         Text C1          Comment C1        Text D1       Comment D1       .......

My approach is to use tPivotToColumnsDelimited. But there is a limitation of only one aggregation column. Due to this I will need to use it twice and then somehow merge the files into one output.
This is what I have done so far. I have used tPivotToColumnsDelimited, first using pivot column as “ID” and aggregate column as “Text”. Then using pivot column as “ID” and aggregate column as “Comment”. There is a bunch of “group by columns” as well.
This has resulted in two similar delimited files (csv).
Ideally I could use tMap component to merge the two CSV files. But I do not have a predefine schema as the data in the files is not static. So I am unable to define a schema for input source to tMap.
Can someone please suggest a way forward?
I’ll be grateful for your help.
Regards,
Vishakha