Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Never look down – The Lookup() Story

cancel
Showing results for 
Search instead for 
Did you mean: 
robert_mika
Master III
Master III

Never look down – The Lookup() Story

Last Update:

Feb 12, 2015 10:05:19 PM

Updated By:

robert_mika

Created date:

Feb 12, 2015 10:05:19 PM

Attachments


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 does it look.png

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 tablethis 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’:

Table1Table2
t1.jpgt2.jpg

Remember ligtbulb.jpg

  • 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:

equal.jpg

 

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:ligtbulb.jpg

  • 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:

outcome.png

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)

Comments
simondachstr
Luminary Alumni
Luminary Alumni

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

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

simondachstr
Luminary Alumni
Luminary Alumni

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
Partner Ambassador
Partner Ambassador

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
Master III
Master III

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
Partner Ambassador
Partner Ambassador

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
Not applicable

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

Not applicable

I was not aware Lookup function is available in Qlikview.

Very Nice explanation. Thanks Robert!

robert_mika
Master III
Master III

Sorry for late replay.

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

Applymap will not work.

Anonymous
Not applicable

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?

Version history
Last update:
‎2015-02-12 10:05 PM
Updated by: