I have a binary load from a qvw and am trying to add a separate excel sheet and then do a lookup so that a few dimensions can be mapped to the values I have added on my excel sheet. When I load my added excel sheet, every single thing returns as 'missing' (I have assigned it to return 'missing' if it can't find a value).
First I do a binary load, and then as shown below I have created two Mapping tables so I can look up the values I need from my excel sheet. ([ERP Material Group Desc2] and [ERP Supplier Parent] are the dimensions in the binary load and excel sheet, and I want to map [ERP Material Group Desc2] to 'Major Commodity Category' and map [ERP Supplier Parent] to 'Vendor Grouping').
Here is what I have put at the very end of the script:
MappingTable1: Mapping Load 'ERP Material Group Desc2', 'Major Commodity Category' FROM [lib://Spend Visibility/10_File_Data\Mapping Sheet.xlsx] (ooxml, embedded labels, table is Sheet1);
MappingTable2: Mapping Load 'Vendor Name' as [ERP Supplier Parent], 'Vendor Grouping' FROM [lib://Spend Visibility/10_File_Data\Mapping Sheet.xlsx] (ooxml, embedded labels, table is Sheet1);
Spend_ERPCommodity2: Load *, ApplyMap('MappingTable1','ERP Material Group Desc2','Missing') as [Desc 2 Commodity Category] Resident Spend_ERPCommodity;
drop table Spend_ERPCommodity; Rename Table Spend_ERPCommodity2 to Spend_ERP_Commodity;
ApplyMap('MappingTable1',"ERP Material Group Desc2",'Missing') as [Desc 2 Commodity Category]
Remove ' ' around ERP Material Group Desc2 and replace it by "". In script it should have brown/orange color. Just for make i clear does your table Spend_ERPCommodity contain field named ERP Material Group Desc2,