topic Re: Multiple vlookup equivalent in QlikView

Multiple vlookup equivalent

mattdonkin — Sat, 16 Nov 2024 18:14:51 GMT

Hi, I have a mapping table with 3 fields - QuestionNumber, Answer, RAG Status - and then an input file which includes QuestionNumber and Answer for multiple respondents.

Initially I was using a straight table with the expression Only([RAG Status]) to return the corresponding RAG status based on the Answer provided.

However, some of the responses are free format text so I cannot use a lookup to provide the RAG status - in these instances I want to do a lookup on the QuestionNumber to pull the RAG Status.

In excel, the formula is similar to =IFERROR(VLOOKUP(Answer,RAG Status,0),(VLOOKUP(QuestionNumber,RAG Status,false)))

Essentially, I want an expression to return the RAG Status based on the Answer but, where that doesn't exist, return the RAG Status based on the QuestionNumber.

I've looked in the forum for solutions using Alt, Aggr, ApplyMap etc but cannot work out which to use and how to apply.

Appreciate any help & advice.

Thanks!

Re: Multiple vlookup equivalent

jyothish8807 — Mon, 10 Aug 2020 12:38:20 GMT

Hi Matt,

may be something like this:

Create two mapping tables one with Answer and RAG Status and other with Question and RAG Status:

[Answer Table]:

Mapping load

Answer,

RAG

from <>

[Question Table]:

Mapping load

Question,

RAG

from <>;

Main:

load *,

Applymap('Answer Table',Answer,Applymap('Question Table', Question,'NA')) as New field

from <>

Re: Multiple vlookup equivalent

mattdonkin — Mon, 10 Aug 2020 14:57:05 GMT

Hi KC, thanks for the response. I'm getting an error saying applymap id not found?

[Answer Table]:
MAPPING LOAD
Answer,
[Benchmark RAG]
FROM
[C:\RAG.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Question Table]:
MAPPING LOAD
QuestionNumber,
[Benchmark RAG]
FROM
[C:\RAG.xlsx]
(ooxml, embedded labels, table is Sheet1);

Main:

LOAD *,

Applymap('Answer Table',Answer,Applymap('Quesion Table',Question,'NA')) as RAG_Lookup

from
[C:\RAG.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Multiple vlookup equivalent

Brett_Bleess — Tue, 25 Aug 2020 15:26:32 GMT

Have a look at the following Design Blog post and Help links, they may give you the information you need to further troubleshoot things:

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/MappingFunctions/ApplyMap.htm

Regards,
Brett