Hi,
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;
Spend_Supplier2:
Load *,
ApplyMap('MappingTable2','ERP Supplier Parent','Missing') as [Vendor Desc 2 Commodity Category]
Resident Spend_Supplier;
drop table Spend_Supplier;
Rename Table Spend_Supplier2 to Spend_Supplier;
Why is everything returning as missing, as I have checked manually that most of them should be returning the value they are mapped to?
¨