Never look down – The Lookup() Story


    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 (25 articles)