topic Re: Multiple lookup in QlikView

Multiple lookup

Anonymous — Fri, 26 Sep 2014 10:51:00 GMT

Hi Community,

how can i do a multiple lookup?

I tried following without luck :

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

Re: Multiple lookup

anbu1984 — Fri, 26 Sep 2014 10:55:22 GMT

Can you show script and sample data.

Re: Multiple lookup

Anonymous — Fri, 26 Sep 2014 11:04:10 GMT

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?

Re: Multiple lookup

deepakqlikview_123 — Fri, 26 Sep 2014 11:28:12 GMT

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

Thanks

Re: Multiple lookup

Anonymous — Fri, 26 Sep 2014 14:14:10 GMT

Hey Deepak,

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

Re: Multiple lookup

anbu1984 — Fri, 26 Sep 2014 14:18:16 GMT

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

Re: Multiple lookup

Anonymous — Fri, 26 Sep 2014 14:23:38 GMT

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

Re: Multiple lookup

flipside — Fri, 26 Sep 2014 14:54:43 GMT

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