Qlik Community

Ask a Question

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
laurenairproduc
Contributor
Contributor

ApplyMap() Question on Qlik Sense

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?

3 Replies
DavidŠtorek
Creator III
Creator III

Hi, maybe try one following thing

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,

laurenairproduc
Contributor
Contributor

Hi David,

Thank you for your response. I got it to work.  I had to write each applymap function like this:

ApplyMap('MappingTable1',[ERP Material Group Desc2],'MISSING') as [Desc 2 Commodity Category]

And each dimension in the Mapping Load statement needed " "  instead of ' '

Thanks!

DavidŠtorek
Creator III
Creator III

Glad to help ¨

Please mark as correct answer to help allso others.