27 July 2022 How do I join tables 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 joining tables in Qlik Sense: The correct answer is D: Right Join The trick in this question was the load order of the tables. In order to make sure only CarID’s with a target were shown, we needed to right join the first loaded table with the car sales to the table with car targets. All other answers would have kept the CarID’s without a target in the first loaded table. Most often when creating a data model there will be the need to merge tables with each other, whether it is to enrich the table with extra values or as in this case to only load values we want to have loaded. To merge the tables the join statement is used before the load statement. The join statement can also be preceded by one of the following prefixes: inner, outer, left, right. These will determine how the previously loaded table will be merged with the table being loaded. The join statement is then followed by the table we would like to compare the values being loaded with. This has to be a previously loaded table. So the full syntax is: [inner|outer|left|right] Join [(Tablename)] (loadstatement|selectstatement) To visualize let’s take the following two tables and see how each join works: Inner Join An inner join will only keep the rows that are matching in both tables: Outer Join An outer join generates all combinations between both tables: Left Join A left join keeps only those field values in the second table that match the link value (key field) of the first loaded table: Right Join A right join will only keep field values of a previously loaded table matching the link value (key field) of the table being loaded: Now let’s apply this knowledge to the question. We have loaded the Car Sales and the Car Targets tables in that order. We now only want to keep the CarID’s of cars which have a target. This means we have to use the Car Targets table to determine this. So we have to keep the CarID’s from the Car Targets table, which is a right prefix, and since the only one in the question is a right join, that should be the answer: So the final script will be: Other things to keep in mind when joining tables in Qlik Instead of join we could also use keep. This works the same way, but instead of merging the tables into one, it will keep both tables (see the explanation above). Concatenate is another way to join tables. Hereby there is no inner, outer, left, right prefix needed, concatenate will simply append the rows from one table to the other. A rule of thumb is, if you have to add columns to the table you use join, if you have to add rows to the table, you use concatenate. If you don’t specify a prefix and just use join, Qlik will automatically assume an outer join. It is advisable to always specify the type of join to keep your scripting clear and understandable. 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