Never look down – The Lookup() Story

[robert_mika]

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)

[simondachstr]

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

[rbecher]

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

[simondachstr]

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.

[SergeyMak]

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?

[robert_mika]

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"

[SergeyMak]

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?

[Anonymous]

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

[#9]

I was not aware Lookup function is available in Qlikview.

Very Nice explanation. Thanks Robert!

[robert_mika]

Sorry for late replay.

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

Applymap will not work.

[Anonymous]

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?