Forum Discussion
Merging tables with empty cells
Hi Simon_29 ,
I'm assuming it's only your second table that has order number gaps. If so, then the best thing I think would be to merge the two table on order number, but do a FULL OUTER merge type. This will match up records where they can be matched, but will also keep all the rows that couldn't be matched upon which you can apply some logic to label them as unmatched or similar.
If you have blank order numbers in both tables, then you'll probably want to replace blank values in table1 with "noOrderNumber1", and the same in table2 with "noOrderNumber2" or similar, then perform the FULL OUTER merge as before. This will prevent nulls matching with nulls and creating a mess.
Pete
Hi Simon_29 ,
I'm assuming it's only your second table that has order number gaps. If so, then the best thing I think would be to merge the two table on order number, but do a FULL OUTER merge type. This will match up records where they can be matched, but will also keep all the rows that couldn't be matched upon which you can apply some logic to label them as unmatched or similar.
If you have blank order numbers in both tables, then you'll probably want to replace blank values in table1 with "noOrderNumber1", and the same in table2 with "noOrderNumber2" or similar, then perform the FULL OUTER merge as before. This will prevent nulls matching with nulls and creating a mess.
Pete
