Picking non-null values from Key matched pair

[vinnz]

Hello,
We currently get pairs of records from an external source where one is sort of a primary record and the other is secondary and we need to dedupe then based on key columns. We would need to pick a value from the secondary record iff there isnt any present on the primary record.

An example is below. I've mentioned only 5 columns in the example but its more like 40 columns in the data being as of now.

Code: Select all

Input:
Flg  Key1      key2    col1    col2       col3           col4      col5     col6
Y    A1        B1      3                  2005-12-01               ABC      50
     A1        B1                         2005-12-03     45                 5

Expected Result:
Flg  Key1      key2    col1    col2       col3           Col4      col5     col6
Y    A1	     B1      3                  2005-12-01     45        ABC      50

Earlier when there were only 3-4 such columns, I managed to do it with a Server job using the combination of sort, Key Change and Stage variables. Is there a better way to do it ?

Unfortunately, we do not have access to create temporary tables at this time and the external vendor sending us the file is either unwilling or unable to dedup at the source.

Thanks in advance.

[vinnz]

I suppose I could also split up the pair, rename columns on one link, join the records back on the key and then compare each of the values in a downstream transformer/modify stage. Would that be the best way to do it?