How to use keep and join in Qlik Sense

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 B: All fields in the table Solutions are loaded, limited to the row with the highest SolutionID.

The answer to this question brings us back to the overview of keeps, joins and all their possibilities. So let’s dive in and see what they do:

Combining tables:

When there is the need to combine tables in the data model, the prefixes Join, Keep or Concatenate are used. Join and Concatenate are used to merge the tables, Keep will keep and store both tables in the resulting data model.

A quick reminder is that Join is used to add columns to a table and Concatenate if you want to add rows.

Inner, Outer, Left, Right:

The Join and Keep prefixes are followed by the prefixes left, right, inner and outer (outer only in case of a join).

• Left

The left prefix retains rows from the left (previously loaded or target) table and only adds matching values from the right (currently loaded) table.

• Right

The right prefix retains rows from the right (currently loaded) table and only adds matching values from the left (previously loaded or target) table.

• Inner

The inner prefix retains only rows matching both the left( previously loaded or target) table and the right (currently loaded) table.

• Outer

The Prefix Outer will retain all rows from both tables, even if they don’t match. This is also the reason outer keep doesn’t exist. You are in that case just loading the tables as normal.

Joins and keeps in practice:

To put this all into perspective, let’s take the following tables and see how all the different joins and keeps work in practice:

Joins:

• Inner Join

An inner join will only keep the rows that are matching in both tables based on the key value(s):

• 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:

Keeps:

• Inner Keep

An inner keep will only keep the rows that are matching in both tables based on the key value(s):

• Left Keep

A left keep keeps only those field values in the second table that match the link value (key field) of the first loaded table:

• Right Keep

A right keep will only keep field values of a previously loaded table matching the link value (key field) of the table being loaded:

Answering the question:

Now we can examine the question and check what happens in the answer. Since the target table of the Inner Keep isn’t specified Qlik automatically assumes the previous loaded table as target table. This is always the case when not specifying a Join or Keep, so keep that in mind. As best practice we recommend to always specify the target table to keep the script easy to read and understandable.

Next in the Inner Keep load the Max(SolutionID) is loaded as a field and aliased to match SolutionID of the previous loaded table. Since Max() is an aggregation we need to do a group by, in this case done on the CustomerID. It is not necessary to load CustomerID as a field in this table to do the group by on. The resulting table will be a table of just one column with SolutionID’s, calculated as the max SolutionID per CustomerID. As we follow the process in the following figures we see that:

First the temp table is created as mentioned above:

Next the Inner Keep will only keep the matching key field in both tables. So the SolutionID’s which are present in the Temp table are also the only ones kept in the Solutions table.

And after the drop statement for the temp table only the Solutions table with all its fields remains, however limited to the max SolutionID from the Temp table.

Now you know how to limit a previously loaded table quickly on max values.

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: