O/p in mentioned Format

balu536 · Post by **balu536** » Tue Nov 10, 2009 6:02 am

Hi,
I have a requirement where i need to merge the data coming under 2 columns into one.i.e say if i have 2 columns
COLA COLB
A 10
A 20
A 30
A 40
B 50
B 60
B 70

and the output should be presented in one column i.e
COL
A
10
20
30
40
B
50
60
70

Please help in achieving the logic.

Regards,
Balakrishna

gssr · Post by **gssr** » Tue Nov 10, 2009 6:15 am

You can use PIVOT Stage!

balu536 · Post by **balu536** » Tue Nov 10, 2009 6:22 am

Will you please explain me in detail.The o/p with the pivot will be
A
10
A
20
A
30
.
.
B
50
B
60
.
.

Please explain me how i need to proceed after this.

The output here should be in the exact format i.e (10,20,30,40) should exactly come under A and (50,60,70) should come next to B.This order should be strictly followed.

It is something like Parent Child relationship where A,B will be acting as parents and (10,20,30,40,50,60,70) will be their respective childs.

gssr · Post by **gssr** » Tue Nov 10, 2009 6:37 am

After getting output like this,
Col
A
10
A
20
A
30
B
40
B
50
.
.
.

You can use remove duplicate stage (Provided the records in the column 2 have no duplicates!!)

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Nov 10, 2009 7:01 am

Or you can write with field delimiter of newline character with the first column only on change.

You can use a sort stage to order and locate the change.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Nov 10, 2009 7:03 am

Or you can write with field delimiter of newline character with the first column only on change.

You can use a sort stage to order and locate the change.

balu536 · Post by **balu536** » Tue Nov 10, 2009 7:07 am

Saintah,
Will you please explain it in clear.I mean in which stage you were mentioning the change to be done.

Regards,
Balakrishna

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Nov 10, 2009 7:09 am

Can you let us know what have you tried so far ?

balu536 · Post by **balu536** » Tue Nov 10, 2009 7:55 am

I tried the logic using the stage variables.PFB the details of the same.Initial values for these stage variables are null

svcolA = COLA
prevscolA = svcolA
svcolB = COLB
prevscolB = COLB

On the output part of the transformer stage under the single column,i have written the logic as

(If svcolA = prevscolA then COLB else prevscolA)

and with the mentioned input in early post the output i'm getting is
A
10
20
30
B
50
60

The last record in A and B are missing.

balu536 · Post by **balu536** » Tue Nov 10, 2009 7:58 am

A small correction in early post

On the output part of the transformer stage under the single column,the logic is

(If svcolA = prevscolA then prevscolB else COLA)

Regards,
Balakrishna

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Nov 10, 2009 8:10 am

Try to write current values in the output rather than previous ones.

balu536 · Post by **balu536** » Tue Nov 10, 2009 10:35 pm

If we'll be doing so then we'll miss the first set of values in each group (i.e 10,50).The output will be

A
20
30
40
B
60

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Nov 11, 2009 3:48 am

Write the two columns in two separate links.

First link with A and B - only when they change.

Second with the numbers - always.

Funnel them together to get your result.