One Star

Transpose Column to Rows

Hi,
how to transpose table as below
INPUT :
NOID RANK YEAR1 YEAR2 YEAR3
122A 1 15 12 13
122A 2 12 10 11
122B 1 13 11 12
122B 2 19 11 15

TRANSPOSE TO OUTPUT:

NOID RANK YEAR AMOUNT
122A 1 1 15
122A 1 2 12
122A 1 3 13
122A 2 1 12
122A 2 2 10
122A 2 3 11
122B 1 1 13
122B 1 2 11
122B 1 3 12
122B 2 1 19
122B 2 2 11
122B 2 3 15

for information currently i'm using version 4.1.2 but i can't see compenent tPivotToRows.Please advise.TQ
5 REPLIES
One Star

Re: Transpose Column to Rows

Hi
Try to download tUnPivotRow at Exchange.
tUnPivot will change columns into rows.
Deploy a new component
Specify a ?User components folder? in TOS preferences (preferences/Talend/Components)
Copy your newly downloaded component folder in the user components folder
After launch, you should see your component in folder ?components/user? in org.talend.designer.components.localprovider plugin
your component should also appear in the Palette in the job designer (in a family folder if relevant)
Regards,
Pedro
One Star

Re: Transpose Column to Rows

i'm still searching the directory to copy the component.
One Star

Re: Transpose Column to Rows

Hi
Specify a ?User components folder? in TOS preferences (preferences/Talend/Components).
For example, set "C:\UserComponents\".
Create a directory called "tUnPivotRow" in "C:\UserComponnets\".
Unzip the downloaded zip file and copy the four files into "C:\UserComponnets\tUnPivotRow".
Now open preferences/Talend/Components in TOS.
And click "apply" button.
Wait a moment.
You can get tUnPivotRow component in TOS pallete.
And i have tested it which works fine in TOS5.0.1.
Regards,
Pedro
One Star

Re: Transpose Column to Rows

Hello
Just try the sample as described in screenshot
but doesn't work... on 5.0.2
Exception in thread "main" java.lang.Error: Problèmes de compilation non résolus : 
id_shop ne peut pas être résolu ou n'est pas un champ
id_product ne peut pas être résolu ou n'est pas un champ
id_prod ne peut pas être résolu ou n'est pas un champ
year ne peut pas être résolu ou n'est pas un champ
month ne peut pas être résolu ou n'est pas un champ
pivot_key ne peut pas être résolu ou n'est pas un champ
pivot_value ne peut pas être résolu ou n'est pas un champ
pivot_value ne peut pas être résolu ou n'est pas un champ
Erreur de syntaxe, insérez "}" pour effectuer Bloc

Smiley Sad
Employee

Re: Transpose Column to Rows

Use this approach. Treat everything as Strings and not Numbers. Convert back later if neeeded as Numbers:
- use tMap to contactenate the Year amounts into a single field so your output from tMap is 3 columns as follows:
NOID -> NOID
RANK -> RANK
AMOUNT -> Year1 +","+ Year2 +","+ Year3"
- use tNormalize to normalise the data on "AMOUNT". This will result in a row of data in the format:
"NOID","RANK","AMOUNT"
So for the First row, the result is 3 rows:
122A,1,15
122A,1,12
122A,1,13
Now take each of these rows through tMap and add the "YEAR" column. The value of the Year column will be a Modulo of the Row number, so use a ternary expression with a Sequence Number variable:
Declare variable in tMAP -> Numeric.Sequence("s1",1,1) -> Var.Var1
Use the Expression in the Year output schema value:
((Var.var1 % 3) == 0) ? 3 : (Var.var1 % 3)
This will result in the final output:
"NOID","RANK","YEAR","AMOUNT"
122A,1,1,15
122A,1,2,12
122A,1,3,13
122A,2,1,12
122A,2,1,10
122A,2,1,11
...
etc.
See image