Help Required

[balu536]

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

[chulett]

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

[balu536]

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

[mekrreddy]

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

[balu536]

Can you please explain the same in detail

[mekrreddy]

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

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

[Sainath.Srinivasan]

Sourt -> Sort -> Transformer -> Output

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

[chulett]

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.

[balu536]

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

[priyadarshikunal]

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

[balu536]