QlikView Expressor: Using the Lookup Functions

    Originally published on 09-22-2011 03:00 AM

     

    Before discussing the lookup functions and how they are used, let's start by creating a lookup table.

    lookup_table1.png

    This lookup table has four attributes and the place attribute is a unique key.  If you access this table using a Lookup Expression Rule you will use a key value to retrieve the other attributes.  If there is no entry in the lookup table with a specific key value, the Lookup Expression Rule will allow you to write a new entry into the lookup table.  But the Lookup Expression Rule does not give you the ability to modify or delete entries from the lookup table. With a Lookup Function Rule, you have full control over the lookup table and the lookup functions are what you use to manipulate the contents of the lookup table.

    The lookup functions are only available from within a Lookup Rule, although to use them from within a Lookup Expression Rule you would probably need to call a function defined within a Datascript Module, as no matter what you want to do with these functions you will need to write more than a single statement of code (which is all you can include in a Lookup Expression Rule).

    The expressor.lookup.connection is the top-level object that you use to create the other lookup function objects.  It is created using the function get_connection on the lookup object.

    conn = lookup.get_connection("name of lookup table")

    The name of the lookup table, the argument to the get_connection function, is comprised of the name of the project or library that contains the lookup table artifact concatenated to the name of the lookup table artifact, separated by an underscore.  For example, if the Presidents lookup table were in a project (or library) name Government, the argument to the get_connection function would be Government_Presidents.

    Once you have the connection object, you can get the reader, range_reader, writer, updater and deleter objects with functions such as:

    writer  = conn:get_writer()
    updater = conn:get_updater()
    deleter = conn:get_deleter()

    The get_reader and get_range_reader functions require an argument, which is the name of the key.  For the Presidents lookup table, this would be Key, the name of the key and not place the name of the attribute comprising the key.

    reader = conn:get_reader("Key")
    range_reader = conn:get_range_reader("Key")

    Each of the lookup objects implements the method execute.  The arguments to this method differ depending on which object the method is called.

    For the reader and range_reader objects, the argument is a string indexed datascript table in which the index name is the name of the key column(s) (not the key name), and the value is the key value, which you most likely obtain from an input parameter to the Lookup Funtcion Rule. In the Lookup Function Rule, the input and output parameters are populated once you select the lookup table and key, as shown in the following figure,although you may add additional input parameters corresponding to the attributes in the incoming record.

    lookup_table2.png

    To retrieve data related to a specific president, your function call would appear something like the following.

    reader:execute({place=input.place})

    If the key is comprised of multiple attributes, you simply create a table argument with multiple elements.

    reader:execute({key1=val1,key2-val2,...})

    Note that this invocation does not return a value.  To obtain the return, you call the reader:next function, which returns a string indexed table with the data from the lookup and the expressor.lookup.rowid, the unique identifier for the data row in the lookup table.

    president, rowid = reader:next()

    The variable president will contain content similar to the following.

    {place=16,last_name="Lincoln",first_name="Abraham",political_party="Republican"}

    To obtain each value, use the standard table dot notation.

    FirstName = president.first_name

    The rowid is not something you can view or print; you use it as an argument when updating or deleting a row in the lookup table.  If you have used a non-unique lookup table key, you can call reader:next multiple times to retrieve all of the entries returned from the lookup table.

    To write new content to the lookup table you invoke execute method on the writer object.  In this usage, the argument is a string indexed table that has the same structure as the lookup table.  For example:

    rowid, str = writer:execute({place=44,last_name="Obama",first_name="Barack",political_party="Democratic"})

    If the row is successfully written, the rowid is returned.  If there is a problem, rowid is nil and an error message (str) is returned.

    In order to update or delete a row from the lookup table, you must first read the row to obtain the rowid, which identifies the row you want to update or delete.  To update a row in the lookup table, you invoke the execute method on the updater object.  This function call requires two arguments; the first is the rowid of the row you want to update; the second is a string indexed table with the updated content.  This table must include an element for every attribute in a lookup table entry.

    rowid, str = updater:execute(rowid_of_row_to_update,{place=1,last_name="Washington",first_name="George",political_party="not affiliated"})

    And to delete content from the lookup table, you pass the rowid as the argument to execute.

    deleter:execute(rowid_of_row_to_delete)

    Calling execute on the range_reader object may have surprising results.  This call will return a lookup table entry that matches the key, but if there is no such record it returns the record with the next higher key value.  For example,

    reader:execute({place=1})
    president, rowid = reader:next()

    Will return

    {place=1,last_name="Adams",first_name="John",political_party="Federalist"}

    if George Washington's entry is not in the lookup table.  Note that the value of place is inconsistent with the rest of the data.

    The attached document summarizes the Lookup function API's.