Building a cross table

stautner_a — Thu, 25 Feb 2016 10:32:07 GMT

Hi,
i have a csv file in the following format:
Header key1,key2,key3,column1,column2,....,column10
Data
key1_line_1_data,key2_line_1_data,key3_line_1_data,column1_line_1_data,column2_line_1_data,....,column10_line_1_data
key1_line_2_data,key2_line_2_data,key3_line_2_data,column1_line_2_data,column2_line_2_data,....,column10_line_2_data
i want to transform the file so that the endresult should look like
key1,key2,key2,column,data
key1_line_1_data,key2_line_1_data,key3_line_1_data,column_1,column1_line_1_data
key1_line_1_data,key2_line_1_data,key3_line_1_data,column_2,column2_line_1_data
...
key1_line_1_data,key2_line_1_data,key3_line_1_data,column_10,column10_line_1_data
key1_line_2_data,key2_line_2_data,key3_line_2_data,column_1,column1_line_2_data
....

Is it possible to archive this transformation with Talend components and if yes how or have i code it manually? The reason why i am not using a tsplitrow component is, that the column name could vary.
Thanks for helping,
Alexander Stautner

Re: Building a cross table

Anonymous — Thu, 25 Feb 2016 16:37:45 GMT

sure!

this solution is a little hacky, and there are many ways to piviot data with Talend so maybe some other Guru's can weigh in with different solutions.
I've set up the test data like this:

The tMap is where the most of the magic happens.
I take all of the "data columns" and add their name, a comma, then the value followed by a semicolon:

"newColumn" + "," + row1.newColumn + ";" + 
"newColumn1" + "," + row1.newColumn1 + ";" + 
"newColumn2" + "," + row1.newColumn2 + ";" + 
"newColumn3" + "," + row1.newColumn3+ ";" + 
"newColumn4" + "," + row1.newColumn4+ ";" + 
"newColumn5" + "," + row1.newColumn5+ ";" + 
"newColumn6" + "," + row1.newColumn6+ ";" + 
"newColumn7" + "," + row1.newColumn7+ ";" + 
"newColumn8" + "," + row1.newColumn8+ ";" + 
"newColumn9" + "," + row1.newColumn9+ ";" + 
"newColumn10" + "," + row1.newColumn10

Then I use a tNormalize to pivot the data columns on semicolon:

Followed by a tExtractDelimitedFields to split out the name from the data values. Note you need to change the output schema of this component to make room for the split out columns:

finally I print out the data giving what we want:

topic Re: Building a cross table in Talend Studio

Building a cross table

Re: Building a cross table