Qlik Community

Qlik Sense Data Connectivity

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
laurenairproduc
New 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
Contributor III

Re: ApplyMap() Question on Qlik Sense

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
New Contributor

Re: ApplyMap() Question on Qlik Sense

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
Contributor III

Re: ApplyMap() Question on Qlik Sense

Glad to help ¨

Please mark as correct answer to help allso others.