topic Re: Transpose column to row in Talend Studio

Transpose column to row

Anonymous — Sat, 16 Nov 2024 04:13:39 GMT

I have an input like

id,name,sales1,sales2,sales3
123,Tim,abc,pqr,xyz

I need my output as

id,name,sales

123,Tim,abc

123,Tim,pqr

123,Tim,xyz

Please help me.

Re: Transpose column to row

TRF — Wed, 30 Oct 2019 07:57:32 GMT

Use tSplitRow with the following settings and schema:

Re: Transpose column to row

manodwhb — Wed, 30 Oct 2019 07:59:57 GMT

@Matthew ,you can use tUnpivotRow component ,which is open source component. need to download from talend exchange.

You can refer to this documentation to learn how to install a custom component to talend studio.
https://help.talend.com/reader/2AWmA~w4VvlfP3JC7dTR2w/Kvx1JE1dQGJgGhfFoHK8xA

Re: Transpose column to row

Anonymous — Wed, 30 Oct 2019 08:57:23 GMT

Thank you for the replies. How will be the performance? Which will be better tsplitrow or tunpivot row?

Re: Transpose column to row

TRF — Wed, 30 Oct 2019 09:02:17 GMT

tSplitRow is lighter

Re: Transpose column to row

Anonymous — Wed, 30 Oct 2019 11:05:17 GMT

Just modifying my input

id,col1,col2,col3

101,a,b,a

102,a,a,a

103,b,a,b

My output should be like

101,1,a

101,2,b

101,3,a

102,1,a

102,2,a

102,3,a

103,1,b

103,2,a

103,3,b

I have done half part using tnermalize and got the output as

101,a

101,b

101,a

102,a

103,b

103,a

103,b

Re: Transpose column to row

TRF — Wed, 30 Oct 2019 13:36:44 GMT

Still the same:

Re: Transpose column to row

Anonymous — Wed, 30 Oct 2019 14:15:35 GMT

tsplitrow will not work, since I need to get the count as incremented value

Re: Transpose column to row

ankit7359 — Wed, 30 Oct 2019 14:33:11 GMT

Hi @Matthew ,

You can use tmap and add in a field for holding incremental sequence. Also you have to add a if-else statement -> like if the id is 101 then call numeric.sequence else check for 102 then call numeric.sequence etc.. until the number of id's u have... Also in the last else you can give it as 0 as the datatype of the field is numeric/integer.

Thanks,

Ankit

Re: Transpose column to row

TRF — Wed, 30 Oct 2019 14:59:52 GMT

@Matthew tSplitRow works as you have just 3 columns you want to dispatch over 3 rows with an indice varying from 1 to 3.

Here is the result:

.---+------+---.
|  tLogRow_18  |
|=--+------+--=|
|id |indice|col|
|=--+------+--=|
|101|1     |a  |
|101|2     |b  |
|101|3     |a  |
|102|1     |a  |
|102|2     |a  |
|102|3     |a  |
|103|1     |b  |
|103|2     |a  |
|103|3     |b  |
'---+------+---'