Using the GENERIC LOAD statement in Qlik Sense

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about the Qlik GENERIC LOAD statement:

The correct answer is C: A generic load

The database which is used in this question is a so called generic database. In this database the column names are stored as field values in a dedicated column with the corresponding field values stored in a second column. The basic layout of this table is:

KEY|ATTRIBUTE|VALUE

The generic table is mostly used to store data of objects with various attributes. The main benefit being that there is no need for extra columns in case of different attributes. The columns would then contain NULL values for the objects without those attributes. This makes managing different types of objects easier as well as the adding of new and different kinds of information.

However when we load this table into Qlik it will quickly become apparent that this data model is not the easiest or best to use for selections or analyzing in the front end. Let’s have a look at the table from the question if we load that in the script:

The figure above shows the fields loaded. If we now try to make any selections, for example on all vehicles with a displacement of 1600 we end up with the following result:

This gives us Volkswagen as the only dimension we can do analysis on, losing out on all other attributes like color, horsepower, model and model year. In order to make this work for the front-end we have to use the Generic Load statement to load all attributes as a field. A Generic Load statement is quite easy to make by adding Generic in front of the load or select statement. The Generic Load syntax is:

Generic(Load|Select {Key|Attribute|Value})

Or to visualize it as it is written in the Qlik Script Editor:

Here Manufacturer is the Key field, Description the attribute and Value contains the values. After running the script we are now able to properly analyze all attributes as we can see below:

And now we not only know that the displacement of 1600 belongs to a Volkswagen, but that in this example it is of a silver 1991 Volkswagen Jetta.

Simple fix right? Well absolutely, but there are a few things to know and keep in mind about the Generic Load.

A separate table is created for each attribute

The Generic Load creates a separate table for each attribute and corresponding values. These are all linked by the key field. This provides the most optimal way to store the data. The data model of the question will end up looking like the model below:

In most cases it is recommended to not change this, since this provides the least memory consuming load. However if you have to change this and want to combine these into a single table, it is possible to do this with a loop.

Multiple Keys

In case you have multiple keys in the generic table, the generic load will result into a model with synthetic keys. While most of us have learned to get rid of these, the resulting synthetic keys in this case are completely harmless. However if you insist on removing these it is possible to concatenate all keys into a single concatenated key. For example: Autonumber(KeyA & ‘|’ & KeyB & ‘|’ & KeyC) AS Key.

That’s it for this week. See you next Friday?

Want more? Then click the button below for our full archive!