topic Re: Transpose Column to Rows in Talend Studio

Transpose Column to Rows

Anonymous — Sat, 16 Nov 2024 12:28:02 GMT

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

Re: Transpose Column to Rows

Anonymous — Wed, 11 Jan 2012 09:07:42 GMT

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

Re: Transpose Column to Rows

Anonymous — Thu, 12 Jan 2012 00:46:40 GMT

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

Re: Transpose Column to Rows

Anonymous — Thu, 12 Jan 2012 02:34:48 GMT

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

Re: Transpose Column to Rows

Anonymous — Wed, 04 Apr 2012 11:02:56 GMT

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

Re: Transpose Column to Rows

Anonymous — Wed, 04 Apr 2012 11:45:59 GMT

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