If items is in category "yet to be determined" look up category in excel file

[Simon4]

Hi,

I am making a dashboard with data from a live database. During this process I have run in to a problem. In the database not all items are properly categorized and this will never happen, reason being:

We have recently taken on a new ERP system. To categorize an item the item number must be changed otherwise it will stay in a the category "Yet to be determined". Obsolete items will not receive a new number in the system to prevent pollution of the new ERP system. However to make a proper analyses I still need to categorize the items.

So I made an Excel file and manually categorized the obsolete items.  

I want to accomplish the following:

If item is in category : "Yet to be determined" look in excel file "categorized items" and search for category.

I also want to make it merge the categories so I don't end up with two categories of the same like "seals" and "seals"

How do I make this connection and what is the best way to structure the Excel file?

[Mark_Little · Accepted solution]

Hi @Simon4 

You want to look at the applymap function 

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/Mapping...

After you have your mapping table just wrap the apply map in a an if statement. 

If( category = 'Yet to be determined', APPLYMAP(...), category) as category

... being your apply map parameters

[Simon4]

Hi @Mark_Little ,

Thanks for your time. I already found the solution and indeed it was applymap.

Thanks lots,

SV