I am trying to find a way to use a mapping table to patch permanent data gaps from an excel source file using a mapping table. I have been trying to do this using mapping load but am unable to make it work the way that I need.
I have attached two files below - Mapping Issue (excel) has 2 tabs, one with 'source data' and one with 'gap data'. The QVD has my attempt at mapping these together using mapping load and apply_map to try to close the gaps in the source data with data in gap data.
The issue I have here is that the real source data file I have is fixed and the gaps we have in certain fields will permanently be gaps. I am currently manually manipulating the source file to populate the gaps with correct information. It is necessary to overwrite these with the new file each week and repeat the process.
I want to be able to do this once in a 'mapping file' and then use the logic that if the source data file column is blank, look up the ID in the mapping table and populate with the 'gap' data. This would then become a new column of data in my source data load with all values completed.