Multiple rows into one row

[dslearner]

Hi,

Below is my requirement,

Input:

Key1,Col1,Col2,Col3
1,A,Null,Null
1,Null,Null,B
1,Null,B,Null

OutPut:

1,A,B,B

like this I have 15 columns, at any point of time only one column will have value for a particular Key column. I may get 15 rows or even one row. All are varchar fields.

Any thoughts...

Thanks in advance.

[keshav0307]

you can use aggregator stage, to select max of each non key column..


Ques: what will you do if the data is like this:
Key1,Col1,Col2,Col3
1,A,Null,Null
1,D,Null,Null
1,Null,Null,B
1,Null,B,Null
1,D,Null,Null

[dslearner]

I am sure that at any point of time only one value will come in a column for a particular key

Secondly these are varchar fields, I think we can not use aggirigator here.

Thanks.

[mgendy]

you can use transformer stage first to convert null to zero in case of integer , and to empty in case of characters , then use aggregator stage to get max value for integer , and sum for characters , or you can use sum for both , try this , i think it will work

[dr.murthy]

Try to use transforner stage by cretating the two stage variables.
STRV1 - which is used to identifie the unique records
STRV2- which is used to concatenate the col1,1ol2,col3 for the same key column when the fields doesnot have null value.
Finally create a out fields four
F! - contains key value
F2 - contains frist char information, which fetches the value from STRV2 by using FIELD function similarly F3,F4

[ecmtnc]

Has anyone solved that issue?

I have the same problem, but it is more complicated.
I have the problem below

Key1,Col1,Col2,Col3,Col4,Col5,Col6
1,A,Null,Null,3,6,9
1,Null,Null,B 3,6,9
1,Null,B,Null 3,6,9

I want to have :

Key1,Col1,Col2,Col3,Col4,Col5,Col6
1,A,B,B,3,6,9

I don't want to do it with an aggregator because i have so many columns like Col4,Col5,Col6 and also i don't want to do any calculation on these columns.

Any ideas?

Thanks

[chulett]

Want or not want, use an aggregator. Group on the 'no calculation' columns.

[ecmtnc]

Hi,

Thanks a lot for your helps!

We've made it by taking the columns which is going to be calculated to the aggregator stage, and the other ones to a copy stage. Then we merged these with a join, removed duplicates and it worked.

Thanks again.

[chulett]

Always more than one way to skin a cat, as they say, you'll see that technique referred to as a 'fork join' here.