topic Re: LOOKUP in QlikView

LOOKUP

kamal_sanguri — Mon, 05 Oct 2015 16:03:03 GMT

Hi Experts,

I have been using Qlikview for a while, however never used Lookup. I always prefer having joins for lookup.

I tried lookup but could not get the result. I tried below script to get [Emp Name] from table a in table band I want to know what wrong am I doing?

Thanks in advance!!

LOAD [Emp ID],

[Emp Name]

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

LOAD [Emp ID],

Location,

Lookup('[Emp Name]','[Emp ID]',[Emp ID],',a')as name

FROM

(ooxml, embedded labels, table is Sheet2);

Re: LOOKUP

hic — Mon, 05 Oct 2015 16:04:52 GMT

Applymap() is a better option: Don't join - use Applymap instead

HIC

PS There is a comma too much in your Lookup() function and you use the wrong quotes. Try instead

Lookup('Emp Name','Emp ID',[Emp ID],'a') as name

Re: LOOKUP

Anonymous — Mon, 05 Oct 2015 16:05:42 GMT

hi kamal,

try to use applymap:

ApplyMap question

Re: LOOKUP

kamal_sanguri — Mon, 05 Oct 2015 16:35:04 GMT

Perfect!! Thanks

Re: LOOKUP

kamal_sanguri — Mon, 05 Oct 2015 16:42:48 GMT

You suggested using Apply Map, I have one question-How it is better than join or lookup function?

Re: LOOKUP

Anonymous — Tue, 06 Oct 2015 05:42:10 GMT

Hi Kamal,

There is no rule of a thumb that tells you when to use which function/ technique. You should always try what you think is best based on a given scenario.

It all depends on how large is your data sets you want to combine. A JOIN is very flexible / powerful but also a resource intensive. Mapping tables are extremely fast but you won't experience any improvement on small data sets. Usually QlikView developers tend to prefer Applymap to any JOIN simply because of its efficiency. When a document has to be reloaded in a densely populated QDS environment, every second and every MB saved is crucial for achieving optimal performance.

Avoid JOINs, especially on large data sets. You can knock out your server with an audacious JOIN, often leading to the dreaded "General Script Error".
Use Applymap() for translations and mapping of single fields, or multiple Mapping tables for multiple fields in large data sets. Applymap() it is very faster than Lookup().
Advantage of Lookup () is flexible, Use Lookup () if you want to get values from different columns in an previously (Existing) loaded table. It's not as fast as Applymap() but way faster in many cases than a Join.

Re: LOOKUP

hic — Tue, 06 Oct 2015 06:37:55 GMT

Applymap() executes faster, and has a much easier syntax than Lookup(). (With Lookup() you often have problems getting the quotation marks right.)

Applymap() is less error-prone than a join. A join often duplicates records so that calculations return incorrect answers. This never happens with Applymap().

HIC