Page 1 of 1
trouble with pivot
Posted: Tue May 18, 2010 4:00 pm
by datastagenewbie
Hi I am trying to use pivot and am not getting the desired output, i searched the forum but none helped
Here is my input
Col1, Col2
--------------
a,1
a,2
a,3
a,4
b,1
b,5
b,6
And the output I want is
Col
-----
a
1
2
3
4
b
1
5
6
By using pivot I am getting
a
1
a
2
a
3
a
4
b
1
b
5
b
6
Please help!!!
Posted: Tue May 18, 2010 6:49 pm
by chulett
That's not really a pivot. You could "pre-process" the data into a form that the pivot stage would like however, I would assume. Or perhaps do this all with stage variables in the transformer.
I'm short on time (and brains atm) so will leave the gory details to others...
Posted: Tue May 18, 2010 7:06 pm
by datastagenewbie
I used Sorter , and then in the transformer I used this expression.
If keyChange=1 then Col1 : char(10): Col2 Else Col2
Got me the desired results. :-)
Posted: Tue May 18, 2010 8:40 pm
by chulett
Very nice.
![Cool 8)](./images/smilies/icon_cool.gif)
Posted: Fri May 21, 2010 9:08 am
by agpt
can u pls explain the syntax:
If keyChange=1 then Col1 : char(10): Col2 Else Col2
what it does exactly?
Sorry to ask this but I am a new learner so....
Posted: Fri May 21, 2010 9:55 am
by anbu
a,1
a,2
a,3
a,4
b,1
b,5
b,6
If you set the option Create Key Change column in sort to true then sort creates a column keyChange and output of sort is
a,1,1
a,2,0
a,3,0
a,4,0
b,1,1
b,5,0
b,6,0
If keyChange is one then add a newline char between Col1 and Col2 else just Col2
Code: Select all
If keyChange=1 then Col1 : char(10): Col2 Else Col2
Posted: Fri May 21, 2010 9:58 am
by agpt
Thanks