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)

[Anonymous]

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