article Never look down – The Lookup() Story in Member Articles

Never look down – The Lookup() Story

robert_mika — Fri, 13 Feb 2015 03:05:19 GMT

What it is used for?

To return values from another table field based on matching field value from current table.

Typically used when only one field records from another table are needed.

(You are dropping this (second) table later on.(see below) )

How does it look like?

How is it design?

dual - this is not actual part of the function but prefix used to indicate what kind of value this function returns –in this case it means that this function can return numbers and characters
Lookup - function name

and four arguments:

field_name – is the name of a field in the other table – in the table where we want the value to take from
match_field_name- is the name of a field in the other table – this is the matching value to the next argument

<---->

match_field_value - is the name of field in the this table-matching with previous argument
[, table_name] - This is name of the other table (this argument is optional if omitted current table is used)

Preparation:

We have got two tables in Excel file.

For convenience let’s call them ‘Table1’ and ‘Table2’:

Table1	Table2

Remember

To use the script below the Excel file must be saved in the same folder that your qvd file

Otherwise you will need to make changes the this path - [LookUp.xlsx]

Table2:
LOAD Category,
Sales,
Country
FROM
[Lookup.xlsx]
(ooxml, embedded labels, table is Table2);

Table1:
LOAD Category,
Lookup('Country','Category',Category,'Table2') as Country

FROM
[Lookup.xlsx]
(ooxml, embedded labels, table is Table1);
drop table Table2;

Remember to... Look Up!

(This is where the name of this function has another meaning)

Table 2 must be above Table1!

or after Reload you will get script error.

How does it work?

Formula:

Script:

Table2:
LOAD Category,
Sales,
Country
FROM
[Lookup.xls]
(ooxml, embedded labels, table is Table2);

Table1:
LOAD Category,
Lookup('Country','Category',Category,'Table2') as Country

FROM
[Lookup.xlsx]
(ooxml, embedded labels, table is Table1);
drop table Table2;

Remember:

First two arguments and the last one must be enclosed in apostrophes '' .
You need to drop Table2 using: drop table statement or all fields from Table1 will be loaded, bringing unexpected results.

The final tables you should see are like this:

Pros:

You can return any field from second table based on any matched field from first table -

You can look at first field in the table to return third one and to look at third field to return the first one.

Cons:

Little bit confusing with the syntax
Slower than Apply Map
No ‘False’ return – If there is no match a Null is returned.

To overcome this drawback we can wrap the formula in IF function:

If(isnull(Lookup('Country','Category',Category,'Table2')),'No match',Lookup('Country','Category',Category,'Table2')) As Country

Exercise :

Return field ‘Sales’ from Table2 by using ‘Category’ field from Table1.

(Answer at the end of this article)

Appendix:

For many of you coming from Excel world and working with VLOOKUP this syntax is little difficult to understand at first.

Why we can not have:

Take value from this table go to the another table matched the field and return what we need?

So the syntax would have been:

Lookup(Category,Table2,Category,Country)

Would this not be simpler?

This is open question so please state your case....

Answer to Exercise:

Lookup('Sales','Category',Category,'Table2') as Sales

Still feeling hungry?

How To /Missing Manual(17 articles)

Re: Never look down – The Lookup() Story

simondachstr — Fri, 13 Feb 2015 17:50:14 GMT

Thank you for your contribution Robert. I suppose you can also use alt() instead of your large if-statement:

If(isnull(Lookup('Country','Category',Category,'Table2')),'No match',Lookup('Country','Category',Category,'Table2')) As Country

-> alt(Lookup('Country','Category',Category,'Table2'),'No match') AS Country

Re: Never look down – The Lookup() Story

rbecher — Mon, 16 Feb 2015 14:59:30 GMT

The alt function returns the first of the parameters that has a valid number representation...

Re: Never look down – The Lookup() Story

simondachstr — Mon, 16 Feb 2015 16:41:16 GMT

You're right it's not working. Weird - thought as Lookup is returning a dual field it should be fine, it works in the front end e.g.

Re: Never look down – The Lookup() Story

SergeyMak — Fri, 10 Apr 2015 05:32:41 GMT

I usually use ApplyMap and even I can create several mapping tables from a big table if I need.

So, what is the main Pros in comparison with ApplyMap?

Is it faster then create a map table and then use ApplyMap?

Re: Never look down – The Lookup() Story

robert_mika — Fri, 10 Apr 2015 10:57:56 GMT

Mapping table in ApplyMap can have only two columns.

For lookup you can look at any column and return any column.

but Applymap is faster and the syntax is easier to "digest"

Re: Never look down – The Lookup() Story

SergeyMak — Mon, 13 Apr 2015 04:14:57 GMT

Yeah. That's why I'm still thinking do I need it or not. In which cases Lookup is really better then ApplyMap..

What is your opinion?

Re: Never look down – The Lookup() Story

Anonymous — Sun, 21 Jun 2015 21:21:46 GMT

In my experience the actual ApplyMap function usage is 100s times faster than the equivalent LOOKUP but the ApplyMap requires preparation in the form of a MAPPING LOAD. So its all relative to how long does the MAPPING LOAD take to create compared to how many times does the "lookup" logic get called. I always go with MAPPING LOAD / ApplyMap combo. But wouldn't it be great if QlikView would chose which method was best depending on the circumstances.....

Re: Never look down – The Lookup() Story

Mon, 22 Jun 2015 09:55:23 GMT

I was not aware Lookup function is available in Qlikview.

Very Nice explanation. Thanks Robert!

Re: Never look down – The Lookup() Story

robert_mika — Mon, 22 Jun 2015 10:02:16 GMT

Sorry for late replay.

If you got more that two columns you have to use Lookup.

Applymap will not work.

Re: Never look down – The Lookup() Story

Anonymous — Tue, 20 Feb 2018 09:45:33 GMT

How to look-up values from multiple columns using look-up function? i.e I need to know if the column name in the look-up table is dynamic and can be passed as variable?

Re: Never look down – The Lookup() Story

Anonymous — Tue, 15 May 2018 18:59:55 GMT

Be very careful using the LOOKUP function since it can be very slow. One alternative is the ApplyMap function. Each function has its own pros and cons but I've found the LOOKUP function to be very flexible but slow whereas ApplyMap a little more limited but very fast at the point of looking up the value