Multiple vlookup equivalent

[mattdonkin]

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!

[jyothish8807]

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

[mattdonkin]

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);

[Brett_Bleess]

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/Mappi...

Regards,
Brett