3 August 2022

What is a generic load?

Share this message

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:

What is a generic load?

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:

Generic load selections

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:

Generic load seletions

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:

CarData:
GENERIC LOAD
Manufacturer,
Description,
Value
FROM [lib://CarData.QVD]
(qvd)
;

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:

Generic load selections

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:

Generic load data model

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. And remember:

Are these questions too easy for you?

Masters Summit for Qlik 2022 - New Orleans and Madrid

If these questions are too easy for you, then you may be ready for the Masters Summit for Qlik.

At the Masters Summit for Qlik, we take your Qlik skills to the next level in 3 days with deep dives about:

  • Qlik Data Modelling
  • Qlik Scripting
  • Set Analysis and Advanced Aggregations
  • Solution Architecture & Performance
  • Effective Visualizations
  • Performance Tuning
  • Qlik Sense integration

The courses are taught by Qlik veterans Rob Wunderlich, Oleg Troyanksy, Bill Lay, Nick Webster and Bitmetric’s Barry Harmsen. Together, this team brings almost 75 years of Qlik experience and has implemented hundreds of successful projects.

You can find more information and registration on the Masters Summit for Qlik website.

See you next week!

How can we help?

Feel free to contact us if you have any comments or questions.