What is a generic load?

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:

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

Are these questions too easy for you?

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!

• If you have suggestions for questions, we love to hear from you via WhatsApp or at info@bitmetric.nl
• If you’re enjoying these questions and want to work on stuff like this every day (but a bit more challenging), we’re always on the lookout for new colleagues. Check our job openings here.
• For more of these brain teasers, check out our archive of Qlik certification questions and answers.
• Want to stay up to date about everything Qlik? Then sign up for our newsletter below: