3 August 2022 Using the GENERIC LOAD statement in Qlik Sense 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 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: CarData:GENERIC LOAD Manufacturer, Description, ValueFROM [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: 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? More from the Bitmetric team Take your Qlik skills to the next level! Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level. Join the team! Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings. Data Model Friday Qlik Test Prep Script Solution How can we help? Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have. Call us Mail us 4 December 2024 New critical security patches for Qlik Sense Enterprise for Windows A new security vulnerability in Qlik Sense Enterprise for Windows has been disclosed, affecting versions from February 2023 to November 2024. Ensure your systems are updated with the latest patches to protect against this issue. New Release Qlik Vulnerability 27 November 2024 Structured Data vs Unstructured Data The difference between structured and unstructured data is fundamental to data management and analytics. Here’s an overview of the two types. Qlik 8 October 2024 Artificial Intelligence, Machine Learning, and Deep Learning Explained: How They Impact Your Business In today’s rapidly evolving technological landscape, Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL) are transforming industries and redefining how businesses operate. In this blog post, we will break down these three definitions and elaborate on them. AI
4 December 2024 New critical security patches for Qlik Sense Enterprise for Windows A new security vulnerability in Qlik Sense Enterprise for Windows has been disclosed, affecting versions from February 2023 to November 2024. Ensure your systems are updated with the latest patches to protect against this issue. New Release Qlik Vulnerability
27 November 2024 Structured Data vs Unstructured Data The difference between structured and unstructured data is fundamental to data management and analytics. Here’s an overview of the two types. Qlik
8 October 2024 Artificial Intelligence, Machine Learning, and Deep Learning Explained: How They Impact Your Business In today’s rapidly evolving technological landscape, Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL) are transforming industries and redefining how businesses operate. In this blog post, we will break down these three definitions and elaborate on them. AI