Using 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 about the KEEP and JOIN statements in Qlik:

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 with KEEP or JOIN:

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 and Right KEEP and JOIN:

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:

Qlik Join

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

Qlik keep

• 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. See you next Friday?