Need to convert columns to rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
My input data will be from a transformer stage where i will be getting xml files,each xml containing different elements.
so,i am extracting the elements from the xmls under two columns.one column containing element name and other containing the values.
if
xml1: <xml><p>2</p><q>3</q>
xml2: <xml><x>2</x><y>3</y><z>3</z>
As xmls have different structure,i cannot use xml input stage.So,i am extracting the elements using transformer stage.
After extracting from transformer stage,i have the data as
col1 col2
p 2
q 3
x 2
y 3
z 3
Now i need to pivot this data as
p,q -> xml1
2,3
x,y,z ->xml2
2,3,3
So,i generated the rownumber by setting execution mode of transformer to sequential so that data is like
col1 col2 row_num
p,2 1
q,3 1
x,2 2
y,3 2
z,3 2
in the next transformer stage i used the concatenation logic which i mentioned before in this post and thought to sort it in descending order and pick the first record for each key.
But,in this transformer,again the data is getting partitioned and i am facing the problem.
so,i am extracting the elements from the xmls under two columns.one column containing element name and other containing the values.
if
xml1: <xml><p>2</p><q>3</q>
xml2: <xml><x>2</x><y>3</y><z>3</z>
As xmls have different structure,i cannot use xml input stage.So,i am extracting the elements using transformer stage.
After extracting from transformer stage,i have the data as
col1 col2
p 2
q 3
x 2
y 3
z 3
Now i need to pivot this data as
p,q -> xml1
2,3
x,y,z ->xml2
2,3,3
So,i generated the rownumber by setting execution mode of transformer to sequential so that data is like
col1 col2 row_num
p,2 1
q,3 1
x,2 2
y,3 2
z,3 2
in the next transformer stage i used the concatenation logic which i mentioned before in this post and thought to sort it in descending order and pick the first record for each key.
But,in this transformer,again the data is getting partitioned and i am facing the problem.
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
What problem are you facing?
You are getting
p,2,1
q,3,1
Pass col1 & rownum in one stream and col2 & rownum is another stream. Apply the concatenation logic in both transformer stages and get outputs as shown below
1,p
1,p,q
1,2
1,2,3
Get the first record after desc sort & funnel them to get
1,p,q
1,2,3
Remove rownum column if needed. Are you specific about the order of output rows?
You are getting
p,2,1
q,3,1
Pass col1 & rownum in one stream and col2 & rownum is another stream. Apply the concatenation logic in both transformer stages and get outputs as shown below
1,p
1,p,q
1,2
1,2,3
Get the first record after desc sort & funnel them to get
1,p,q
1,2,3
Remove rownum column if needed. Are you specific about the order of output rows?
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
I am exactlly implementing the same logic as you mentioned.
As i generated the rownumber in first transformer by setting execution mode as sequential,when iam further passing col1 and rownumber to another transformer,the data is getting partitioned and the output of concatenation is like
q,1
q,y,2
p,1
p,x,2
p,x,z,2
but actually which should be
p,1
p,q,1
x,2
x,y,2
x,y,z,2
As i generated the rownumber in first transformer by setting execution mode as sequential,when iam further passing col1 and rownumber to another transformer,the data is getting partitioned and the output of concatenation is like
q,1
q,y,2
p,1
p,x,2
p,x,z,2
but actually which should be
p,1
p,q,1
x,2
x,y,2
x,y,z,2
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Deepa, you need to try from your end. Your current output is
If current rownum is same as previous, then concatenate (other) column to stageVar. Otherwise, assign the column to stageVar.
----------------------------------------------------------------------
p 1
p,q 1
After achieveing the above 2 lines, you need
x,2
x,y 2
You have defined the stage vairables correctly (posted earlier). But did you check what those stage variables will be assigned by doing a simple paper work? There is a small error in the way you have defined the stage variables. Correct it and you will be fine.
Now, it is time to think how to use rownum column for concatenation.p1
p,q1
p,q,x2
p,q,x,y2
p,q,x,y,z2
If current rownum is same as previous, then concatenate (other) column to stageVar. Otherwise, assign the column to stageVar.
----------------------------------------------------------------------
p 1
p,q 1
After achieveing the above 2 lines, you need
x,2
x,y 2
You have defined the stage vairables correctly (posted earlier). But did you check what those stage variables will be assigned by doing a simple paper work? There is a small error in the way you have defined the stage variables. Correct it and you will be fine.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
Hi Kandy,kandyshandy wrote:Are you specific about the order of output rows?
I resolved that issue by placing previouskey stage variable after the concatenation stage variable.
Now i need to funnel out the data like 1st record from 1st transformer containing elements and 1st record from other transformer containing values.
transformer1:p,q 1
x,y,z 2
transformer2:2,3 1
2,3,3 2
So that output is like p,q
2,3
x,y,z
2,3,3
in funnel if i am using sort and sort it in descending ,it is giving me the data as 2,3
p,q
2,3,3
x,y,z
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Hi Kandy,
I removed the funnel stage and placed join stage.
I performed inner join based on the key generated and passes the elements and values on two columns
Next in the transformer stage i inserted newline character after elements.
So now i'm getting them in proper order :D .
Thanks for your valuable suggestions.
I removed the funnel stage and placed join stage.
I performed inner join based on the key generated and passes the elements and values on two columns
Next in the transformer stage i inserted newline character after elements.
So now i'm getting them in proper order :D .
Thanks for your valuable suggestions.
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore