DSXchange

Hi all,
I have a requirement which needs to be handled in the datastage.

i/p

KEY1 KEY2 COL1 COL2 COL3 COL4 COL5 COL6 COL7
1 1 NULL NULL NULL MFS NULL NULL NULL
1 2 A7C NULL NULL NULL NULL NULL NULL
2 1 NULL MFG NULL NULL NULL NULL NULL
2 2 NULL NULL XXX NULL NULL NULL NULL
2 3 NULL NULL NULL NULL IPC NULL NULL

o/p

KEY1 COL1 COL2 COL3 COL4 COL5 COL6 COL7
1 A7C NULL NULL MFS NULL NULL NULL
2 NULL MFG XXX NULL IPC NULL NULL

The datatype of all the above columns mentioned is Varchar

Please help me in achieving the above logic


Regards,
Balakrishna

Please spell out your requirement in words, don't make people try to figure it out from the data.

ok fine here goes the requirement

I have a key column (KEY1 and KEY2).For a particular record only one value exist under a single column.I need to group the records based on the key column(KEY1) and we need to merge the multiple records for that particulat Key column to a single record with the Column values of individual records in that particular group(for ex:KEY1=1) populated in the single record.


Regards,
Balakrishna

Use the constraints in transformer and add logic where or use filter if you do not want use transformer, and also make sure there are no spaces in data

Can you please explain the same in detail

From the data I see above you can specify contraints in transformer as

input.key1=1 or (input.key1=2 and input.col2='MFG')

Sourt -> Sort -> Transformer -> Output

Sort by key1
Check all columns with previous using stage variables in xfm.

This is an aggregation, so simple constraints won't solve the problem. I'd look into using the Aggregator to group by your key field and do a max() of the data fields.

Please correct me if i' wrong

All the columns have data types as Varchar and when i use them in Aggregator the output of Max is of datatype double.Do we have any data type preservation technique in Aggregator

Its preserve data type option in aggregator as mentioned in you another post.