Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
mattdonkin
Contributor
Contributor

Multiple vlookup equivalent

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!

Labels (5)
3 Replies
jyothish8807
Master II
Master II

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

Best Regards,
KC
mattdonkin
Contributor
Contributor
Author

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.