DSXchange

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.

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

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.

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

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

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

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

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.

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