Qlikview Lookup Function

This post demonstrates the use of the Qlikview Lookup Function.  For my use case, I am connecting to SQL Server Express 2014, using the AdventureWorks2012 database.  I connect and pull in the “all_objects” system table, then build a second table using the LOAD Resident technique.

Qlikview Version:  Personal Edition 12.0.20100.0 SR2 64-bit Edition (x64)

OS:  Windows 10

Database:  SQL Server Express 2014

Setup – Qlikview Lookup Function:

First, open and save a new Qlikview QVW file, then connect to SQL Server and pull in the all_objects table, using the following LOAD SCRIPT:

Next, add a table box object like the following:

Qlikview Lookup Function

At this point, notice the field “parent_object_id” above.  It would be nice to see the object name and type for this parent_object_id.  Here is where the Qlikview Lookup Function comes into play!

Here is how to accomplish this next step.  Add the following to your LOAD SCRIPT:

Here, we added two additional tables to the script:

  • ObjectHierarchy – created by taking the “object_id” and “parent_object_id” out of all_objects table, and using Resident LOAD to build the new table
  • ParentObject – created again by taking the “parent_object_id” out of the all_objects table, and adding two additional columns using the Qlikview Lookup Function

Qlikview Lookup Function:

  • ParentObjectName – This field was created by Lookup(‘name’, ‘object_id’, parent_object_id, ‘all_objects’)
    • Parameter 1:  ‘name’ – This is the field we are looking up
    • Parameter 2:  ‘object_id’ – This is the field in the lookup table (all_objects) we want to lookup, against a value in our source table (ParentObject)
    • Parameter 3:  parent_object_id – This is the field in our source table (all_objects) we are using to lookup against key field in lookup table (object_id in all_objects table)
    • Parameter 4:  ‘all_objects’ – This is the lookup table we are searching for the desired value (‘name’)
  • ParentOjbectType – Using the same Lookup function as above, pulling in the ‘type_desc’ field from the lookup table
    • Lookup(‘type_desc’, ‘object_id’, parent_object_id, ‘all_objects’)

 

Now, Reload the QVW, and you will then have a data model that looks like the following:

Qlikview Lookup Function

Now, back in your QVW file, add another table box object to display the ParentObject contents:

Qlikview Lookup Function

 

Finally, bringing it all together, if you select a “parent_object_id” in the table box for all_objects table, you will then see the second table box (ParentObject) update to display the object name and object type of the actual “parent_object_id”:

 

Qlikview Lookup Function

 

That should do it!  Please modify to suit your needs.

 

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

 

Leave a Comment