topic ApplyMap() Question on Qlik Sense in Connectivity & Data Prep

ApplyMap() Question on Qlik Sense

Anonymous — Thu, 12 Jul 2018 14:37:22 GMT

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?

Re: ApplyMap() Question on Qlik Sense

DavidŠtorek — Fri, 20 Jul 2018 13:07:04 GMT

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,

Re: ApplyMap() Question on Qlik Sense

Anonymous — Fri, 20 Jul 2018 13:22:20 GMT

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!

Re: ApplyMap() Question on Qlik Sense

DavidŠtorek — Mon, 23 Jul 2018 05:59:59 GMT

Glad to help ¨

Please mark as correct answer to help allso others.