Transposing data and merging two files

Transposing data and merging two files

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

Highlighted
Moderator

Re: Transposing data and merging two files

Hi Vishakha,
Could you please elaborate your case with an example with input and expected output values?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

Re: Transposing data and merging two files

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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog