Re: Multiple lookup - Qlik Community

Anonymous · ‎2014-09-26

Hi Community,

how can i do a multiple lookup?

I tried following without luck :

Lookup ('ComponentName', 'ProductID', Lookup ('ProductCategory', 'ProductID', ProductID, 'ProductTable') , 'ComponentTable')

anbu1984 · ‎2014-09-26

Can you show script and sample data.

Anonymous · ‎2014-09-26

Hi,

cant show sample data, script is just loading three tables and making an lookup at one of this tables (Product).

With the result of this lookup i want to lookup at the second table (Component).

Additionaly when doing a simple lookup i will get erverytime only one result.

But there are also use cases where more than one value can be looked up, how to achive this?

deepakqlikview_123 · ‎2014-09-26

try applymap function, experts please correct me if I am wrong.

Thanks

Anonymous · ‎2014-09-26

Hey Deepak,

this is not really helping me, i need just an lookup where the third parameter is an result of an previous lookup.

anbu1984 · ‎2014-09-26

Your example doesn't look correct. Your inner lookup retrieves ProductCategory based on ProductID and the same is used against ProductID in outer lookup?

Anonymous · ‎2014-09-26

Yes it should be like this,

Lookup ('ComponentName', 'ComponentID', Lookup ('ComponentID', 'ProductID', ProductID, 'ProductTable'), 'ComponentTable')

but that still not change the fact that it seems that nested lookups are not possible in qlikview

flipside · ‎2014-09-26

I don't think that is the case, the example below seems to work.

ProductTable:

Load * inline [
ProductID, ProductCategory, ComponentID
1, A, 123
2, B, 123
3, A, 333
4, C, 541
5, B, 221
];

ComponentTable:
Load * inline [
ComponentID,ComponentName
123, ABC
333, DEF
541, GHI
221, JKL
];

Data:
Load
Rowno() as ID,
Lookup('ComponentID','ProductID',rowno(),'ProductTable') as ID_output_lkp1,
Lookup('ComponentName','ComponentID',Lookup('ComponentID','ProductID',rowno(),'ProductTable'),'ComponentTable') as ID_output_lkp2
autogenerate 5;

I am sure there is a better way of doing what you want unless you can give more info to suggest otherwise. ApplyMap or left joins should do what you need.

flipside