One Star rjs
One Star

[resolved] Transpose Rows to Columns

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)
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 and $colB=$tPerlFlex_1 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

  • Data Integration
24 REPLIES
Employee

Re: [resolved] Transpose Rows to Columns

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,
One Star rjs
One Star

Re: [resolved] Transpose Rows to Columns

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
Employee

Re: [resolved] Transpose Rows to Columns

Doesn't tPivotOutputDelimited fit your need? see 2299
One Star rjs
One Star

Re: [resolved] Transpose Rows to Columns

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

Re: [resolved] Transpose Rows to Columns

Helo. i'am newbe in talend with java Smiley Very Happy
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
Community Manager

Re: [resolved] Transpose Rows to Columns

Hello capricornday
The tPivotOutputDelimited can fit your need. Please see 1714 and learn its usage.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Transpose Rows to Columns

thanks a lot shong.
i read 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
Community Manager

Re: [resolved] Transpose Rows to Columns

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Transpose Rows to Columns

hi shong
it's work.
thanks for your help Smiley Very Happy
regards
capricornday
One Star

Re: [resolved] Transpose Rows to Columns

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
Community Manager

Re: [resolved] Transpose Rows to Columns

Hello varija
The tUnpivotRow component provided by daztop can fit your need. see the related 5698.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Transpose Rows to Columns

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.
Community Manager

Re: [resolved] Transpose Rows to Columns

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/extension_view.php?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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Transpose Rows to Columns

thanks alot :-) Shong.
It worked fine.
thanks
Varija.
One Star

Re: [resolved] Transpose Rows to Columns

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.

Re: [resolved] Transpose Rows to Columns

You can visit http://vikramtakkar.blogspot.in/2013/01/transpose-rows-to-columns-using.html for similar problem.
You can transpose rows to columns using tPivottoOutputDelimited component.
Thanks
Vikram

Re: [resolved] Transpose Rows to Columns

Hi Song,
i m new in talend i have some requirement my input file is like
row1
row2 row2
row3 row3 row3
................................
and i want the output like
.................................
row1 row2 row3
row2 row3
row3
i m using tPivotToColumnsDelimeted but can't resolve the problem, kindly help me ASAP.
Regards
Shailendra
Moderator

Re: [resolved] Transpose Rows to Columns

Hi Shailendra,
For your reuqirement, I think you need the custom component tTurnRow.
Please see the related forum Forum 29877
Best regards
Sabrina

Re: [resolved] Transpose Rows to Columns

Thankyou Sabrina,
it is not in the list of talend, kindly tell the other way or other process to do that.
is there any other component who do the same task as tTurnRow.
row1
row2 row2
row3 row3 row3
................................
and i want the output like
.................................
row1 row2 row3
row2 row3
row3

Re: [resolved] Transpose Rows to Columns

hi xdshi ,
pls give me the solution to do it.
input is-
row1
row2 row2
row3 row3 row3
................................
and i want the output like
.................................
row1 row2 row3
row2 row3
row3
One Star

Re: [resolved] Transpose Rows to Columns

Hi Team,
             i m new in talend, can you please help me out with the following input:
row1    row2     row3   
row4    row5     row6    
row7    row8     row9

and the desired output is:
row1     row4     row7
row2     row5     row8
row3     row6     row9

Re: [resolved] Transpose Rows to Columns

hi team,
i am new to talend i have same problem as ruchita help me out with problem as soon as possible:
r1 r2 r3
r4 r5 r6
r7 r8 r9
output became
r1 r4 r7
r2 r5 r8
r3 r6 r9

Re: [resolved] Transpose Rows to Columns

Hi Team,
             i m new in talend, can you please help me out with the following input:
row1    row2     row3   
row4    row5     row6    
row7    row8     row9

and the desired output is:
row1     row4     row7
row2     row5     row8
row3     row6     row9
One Star

Re: [resolved] Transpose Rows to Columns

Helo. i'am newbe in talend with java Smiley Very Happy
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

Hello..
I want to do this with using tMap.
do you have any idea..?