Applymap did not return the corresponding values as expected

[hidehuman]

I have 2 tables, one is about product data with product code and product category, the other with year, month, account, product code. I want to add info from the first table to the second so that the second table can tell me which product code corresponds to which category. Here is the original code:

Products:
Mapping LOAD
"Product category code" as key,
"Product code" as value
FROM [lib://DataFiles/Product data.xlsx]
(ooxml, embedded labels, table is [Product data]);

Budget:

LOAD
"Year",
"Month",
Account,
"Product code",
Applymap('Products',"Product code",'N/A') as "category_name", 
"Budget"
FROM [lib://DataFiles/Sales budgets.xlsx]
(ooxml, embedded labels, table is [Sales budgets]);

I checked the Product Data table, the "Product category code" was populated with categories. The codes were executed, but not as expected. The category name of the new, third table all returned N/A. Did I input something wrong in the above codes, or did i miss something else?

[sidhiq91]

@hidehuman  Could you please try something like below in your code:

Products:
Mapping LOAD
"Product category code",
"Product code"
FROM [lib://DataFiles/Product data.xlsx]
(ooxml, embedded labels, table is [Product data]);

Budget:

LOAD
"Year",
"Month",
Account,
"Product code",
Applymap('Products',"Product code",'N/A') as "category_name",
"Budget"
FROM [lib://DataFiles/Sales budgets.xlsx]
(ooxml, embedded labels, table is [Sales budgets]);

[RafaelBarrios · Accepted solution]

Hi @hidehuman 

In the mapping table the first field must be the value that you need to compare, and second field the one you will save in your new field.

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/use-mapping-...

so,  it should be:

Products:
Mapping LOAD
"Product code" as value,
"Product category code" as key
FROM [lib://DataFiles/Product data.xlsx]
(ooxml, embedded labels, table is [Product data]);

I doesn’t matter if you use alias or not, the order needs to be that.

hope this helps.

best,

Help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂

[hidehuman]

It worked. Many thanks!

[RafaelBarrios]

Great @hidehuman 
im glad its working now

please remember to mark "accept as solution" to the post that helps you finding the problem and fixing it so more users can find answers faster.

best,