Talend community forum / [resolved] Transpose Rows to Columns

rjs · 2007-10-29 16:03:14

Hi,

I'm trying to simulate a transpose function in TOS 2.2.0 (perl).

Here is an example of my input lines and what I pretend to get on output:

Input
1|A|5
1|B|3
2|A|1

Output
1|5|3
2|1|

Since I didn't find any component to do it directly, nether a topic in the forum talking about this issue, I tried three different approach, but all without success:

1- Using a tAggregateRow component, I can't manipulate correctly the rows to generate all final columns

2- Using a tSortRow+tPerl with a loop to read lines from stdin until rowId change and then print to stdout the final row with all columns, I can't get data from stdin

3- Using a tSortRow+tPerlFlex+tMap+tUniqRow, I have problems accessing the data in tPerlFlex fields (with $tPerlFlex_1[0])

The problem with the third approach seems to be a bug (maybe I'm wrong) using the fields from tPerlFlex_1 array because a get no data. If I say directly that $colA='5' and $colB='3' then I get the pretended result for the first output row, but if I say $colA=$tPerlFlex_1[1] and $colB=$tPerlFlex_1[2] both variables steels empty.

If someone could help me solving this issue with one of these 3 approaches or a new one, I will appreciate.

Thanks in advance.

RJS

mhirt · 2007-10-29 16:15:05

Hello,

This transformation is a Denormalization : you can use directly the tDenormalize (see documentation).
Someone of the Perl team will probably answer about the bug in approach 3.

Regards,

rjs · 2007-10-29 16:50:07

Thanks mhirt!

I'd passed my eyes on tDenormalize without reading well the documentation and I thought it wasn't for my issue. Now I've read correctly and I think it solves my problem.

Once again, thanks for your quickly answer.

RJS

plegall · 2007-11-22 18:09:39

Doesn't tPivotOutputDelimited fit your need? see [Bugtracker, feature 2299, fixed] New component tPivotOutputDelimited

rjs · 2007-11-22 19:05:44

The answer is no. Thanks anyway, but I solved the problem with tDenormalize.

RJS

capricornday · 2009-03-17 08:45:36

Helo. i'am newbe in talend with java

i have a mySql table like this :
init descrip value
A limit 9
A rate 0
B limit 8
B rate 0

and i want to make output like this:

init limit rate
A 9 0
B 8 0

i've try used tDenormalize and put :
column delimiter merge same value
descrip "" [ ]

but didn't work.
do you have any solution?

regards,

capricornday

shong · 2009-03-17 09:21:18

Hello capricornday

The tPivotOutputDelimited can fit your need. Please see [Forum, topic 1714] tPivot and learn its usage.

Best regards

shong

capricornday · 2009-03-17 10:04:59

thanks a lot shong.

i read [Forum, topic 1714] tPivot and use it.
but the output of tPivot is delimited file.
if i want to make to another mySql table,
i must convert it or there's another way to do it?

regards

capricornday

shong · 2009-03-17 10:36:12

Hello

but the output of tPivot is delimited file.
if i want to make to another mySql table,

After you output the result to a file, Using a tfileInputDelimited component to read the file and insert the data into DB.
eg:
do your pivot
|
onSubJobOK
|
tFileInputDelimited-->tMysqlOutput

Best regards

shong

capricornday · 2009-03-17 12:44:00

hi shong

it's work.
thanks for your help

regards
capricornday

varija · 2009-12-24 15:28:05

Hi I am new to Talend.I need to to convert columns to rows
for example:

input is

id first_name last_name phone_number
1 Varija Hegde 123456
2 Srini Jala 45678

I want output to be

1 first_name varija
1 last_name hegde
1 phone_number 123456
2 first_name srini
2 last_name jala
2 phone_number 45678

Can anyone help me on this

Last edited by varija (2009-12-24 15:30:35)

shong · 2009-12-25 03:30:16

Hello varija

The tUnpivotRow component provided by daztop can fit your need. see the related [Forum, topic 5698] Unpivot / Talend component question.

Best regards

shong

varija · 2009-12-26 10:33:39

Thank you Shong.
I dont see tUnpivotrow in talend.I can see only tpivotToColumns under file-->output category when filter for pivot.
I am using java project in talend open source 3.2.2.r33000.
Please let me know Iam missing something.

Thanks
Varija.

Last edited by varija (2009-12-26 10:47:59)

shong · 2009-12-26 13:01:12

Hello Varija
tUnpivotrow component is not official component, it is created by daztop. You can download it from the below url:
http://talendforge.org/exchange/tos/ext … hp?eid=148
To install a external component, simple follows steps:
1)Download the component from url as above, unzip that archive file.
2)Copy the component folder, go to your installation direcotory\plugins\org.talend.designer.components.localprovider_3.0.3.r21383\componentsand paste it.
3)Restart TOS/TIS.

Best regards

shong

varija · 2009-12-28 13:19:13

thanks alot :-) Shong.
It worked fine.

thanks
Varija.

naga · 2011-05-29 00:44:25

Hi All,

I am very new to Talend and learning the things. I have exactly the same requirement as Varija and trying to use tUnpivotrow component. Whenever I add any column to the key field area it is giving "USERID cant be resolved or it is not a field" (USERID is a key field in my example). Someone please help me in this regard.
I am reading the data from a flat file which has about 15 field and I would like to use the USERID column as a key column and would normalize all the other columns.

vikram.takkar@yahoo.co.in · 2013-01-14 14:07:26

You can visit http://vikramtakkar.blogspot.in/2013/01 … using.html for similar problem.

You can transpose rows to columns using tPivottoOutputDelimited component.

Thanks
Vikram

Announcement

Unanswered posts

#1 2007-10-29 16:03:14

[resolved] Transpose Rows to Columns

#2 2007-10-29 16:15:05

Re: [resolved] Transpose Rows to Columns

#3 2007-10-29 16:50:07

Re: [resolved] Transpose Rows to Columns

#4 2007-11-22 18:09:39

Re: [resolved] Transpose Rows to Columns

#5 2007-11-22 19:05:44

Re: [resolved] Transpose Rows to Columns

#6 2009-03-17 08:45:36

Re: [resolved] Transpose Rows to Columns

#7 2009-03-17 09:21:18

Re: [resolved] Transpose Rows to Columns

#8 2009-03-17 10:04:59

Re: [resolved] Transpose Rows to Columns

#9 2009-03-17 10:36:12

Re: [resolved] Transpose Rows to Columns

#10 2009-03-17 12:44:00

Re: [resolved] Transpose Rows to Columns

#11 2009-12-24 15:28:05

Re: [resolved] Transpose Rows to Columns

#12 2009-12-25 03:30:16

Re: [resolved] Transpose Rows to Columns

#13 2009-12-26 10:33:39

Re: [resolved] Transpose Rows to Columns

#14 2009-12-26 13:01:12

Re: [resolved] Transpose Rows to Columns

#15 2009-12-28 13:19:13

Re: [resolved] Transpose Rows to Columns

#16 2011-05-29 00:44:25

Re: [resolved] Transpose Rows to Columns

#17 2013-01-14 14:07:26

Re: [resolved] Transpose Rows to Columns

Board footer