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! Enjoying these challenges? We have many more, and we’ll even pay you to solve them 😉 Check out our job opening. 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 25 April 2024 Over 3000 Qlik Sense servers still vulnerable to Cactus ransomware In November of last year we reported on how two critical vulnerabilities in Qlik Sense Enterprise for Windows were exploited in the Cactus ransomware campaign. As of today, over 3000 vulnerable Qlik Sense servers remain connected to the Internet. New Release Qlik Vulnerability 29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution 22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization
25 April 2024 Over 3000 Qlik Sense servers still vulnerable to Cactus ransomware In November of last year we reported on how two critical vulnerabilities in Qlik Sense Enterprise for Windows were exploited in the Cactus ransomware campaign. As of today, over 3000 vulnerable Qlik Sense servers remain connected to the Internet. New Release Qlik Vulnerability
29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution
22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization