Need to convert columns to rows

[kandyshandy]

pivot_column = 1 for file 1
pivot_column = 2 for file 2
pivot_column = 3 for file 3..

will this resolve your problem?

[deepa.y]

Yes my issue will be resolved if pivot_column have different values after concatenation.
a 1
a,b 1
a,b,c 1

x 2
x,y 2
x,y,z 2

[kandyshandy]

resolved?

Tell me your job design. How are you handling multiple files?

[deepa.y]

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.

[kandyshandy]

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?

[deepa.y]

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

[kandyshandy]

that means you have not selected the right partitioning/sorting method in the second transformer.

Atleast, try in sequential mode and see if everything looks fine.

[deepa.y]

Ya i also tried setting sequential mode in second transformer and it is giving the output as
p1
p,q1
p,q,x2
p,q,x,y2
p,q,x,y,z2

[kandyshandy]

Deepa, you need to try from your end. Your current output is

p1
p,q1
p,q,x2
p,q,x,y2
p,q,x,y,z2

Now, it is time to think how to use rownum column for concatenation.

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.

[deepa.y]

Thanks Kandy for your valuable time .I will try to find out and let you know.

[deepa.y]

Are you specific about the order of output rows?

Hi Kandy,
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

[kandyshandy]

did you find a way?

[deepa.y]

I tried funnel stage by setting different properties.But the data is received as the values first and then the elementsi.e data from second link first and then the first link.

2,3
p,q

2,3,3
x,y,z

[deepa.y]

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.

[kandyshandy]

You achieved.... not the right way though. May be next time