Combining tables with Join and Keep

A join is an operation that uses two tables and combines them into one. The records of the resulting table are combinations of records in the original tables, usually in such a way that the two records contributing to any given combination in the resulting table have a common value for one or several common fields, a so-called natural join. In Qlik Sense, joins can be made in the script, producing logical tables.

It is possible to join tables already in the script. The Qlik Sense logic will then not see the separate tables, but rather the result of the join, which is a single internal table. In some situations this is needed, but there are disadvantages:

• The loaded tables often become larger, and Qlik Sense works slower.
• Some information may be lost: the frequency (number of records) within the original table may no longer be available.

The Keep functionality, which has the effect of reducing one or both of the two tables to the intersection of table data before the tables are stored in Qlik Sense, has been designed to reduce the number of cases where explicit joins need to be used.

Join

The simplest way to make a join is with the Join prefix in the script, which joins the internal table with another named table or with the last previously created table. The join will be an outer join, creating all possible combinations of values from the two tables.

Example:  

The resulting internal table has the fields a, b, c and d. The number of records differs depending on the fieldvalues of the two tables.

For more information, see Join.

Using Join

The explicit Join prefix in the Qlik Sense script language performs a full join of the two tables. The result is one table. Such joins can often result in very large tables.

Do the following:

1. Open the Advanced Scripting Tutorial app.
2. Add a new script section in the Data load editor.
3. Call the section Transactions.

4. Under DataFiles in the right menu, click Select data.

5. Upload and then select Transactions.csv.
Tip noteUnder Field names, make sure that Embedded field names is selected to include the names of the table fields when you load the data.
6. In the Select data from window, click Insert script.
7. Upload and then select Salesman.xlsx.
8. In the Select data from window, click Insert script.

Your script should look like this:

LOAD "Transaction ID", "Salesman ID", Product, "Serial No", "ID Customer", "List Price", "Gross Margin" FROM [lib://DataFiles/Transactions.csv] (txt, codepage is 28591, embedded labels, delimiter is ',', msq); LOAD "Salesman ID", Salesman, "Distributor ID" FROM [lib://DataFiles/Salesman.xlsx] (ooxml, embedded labels, table is Salesman);
9. Click Load data.
10. Open the Data model viewer. The data model looks like this:

However, having the Transactions and Salesman tables separated may not be the required result. It may be better to join the two tables.

Do the following:

1. To set a name for the joined table, add the following line above the first LOAD statement:

Transactions:

2. To join the Transactions and Salesman tables, add the following line above the second LOAD statement:

Join(Transactions)

Your script should look like this:

Transactions:
LOAD
    "Transaction ID",
    "Salesman ID",
    Product,
    "Serial No",
    "ID Customer",
    "List Price",
    "Gross Margin"
FROM [lib://DataFiles/Transactions.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Join(Transactions)
LOAD
    "Salesman ID",
    Salesman,
    "Distributor ID"
FROM [lib://DataFiles/Salesman.xlsx]
(ooxml, embedded labels, table is Salesman);

3. Click Load data.
4. Open the Data model viewer. The data model looks like this:



All the fields of the Transactions and Salesman tables are now combined into a single Transactions table.

Keep

One of the main features of Qlik Sense is its ability to make associations between tables instead of joining them, which reduces space in memory, increases speed and gives enormous flexibility. The Keep functionality has been designed to reduce the number of cases where explicit joins need to be used.

The Keep prefix between two LOAD or SELECT statements reduces one or both of the two tables to the intersection of table data before they are stored in Qlik Sense. The Keep prefix must always be preceded by one of the keywords Inner, Left or Right. The selection of records from the tables is made in the same way as in a corresponding join. However, the two tables are not joined and will be stored in Qlik Sense as two separately named tables.

For more information, see Keep.

Inner

The Join and Keep prefixes in the data load script can be preceded by the prefix Inner.

If used before Join, it specifies that the join between the two tables should be an inner join. The resulting table contains only combinations between the two tables with a full data set from both sides.

If used before Keep, it specifies that the two tables should be reduced to their common intersection before being stored in Qlik Sense.

Example:  

In these examples we use the source tables Table1 and Table2.

Note that these are examples only. There are no accompanying exercises to be completed in Qlik Sense.

Inner Join

First, we perform an Inner Join on the tables, resulting in VTable, containing only one row, the only record existing in both tables, with data combined from both tables.

Inner Keep

If we perform an Inner Keep instead, we will still have two tables. The two tables are associated via the common field A.

For more information, see Inner.

Left

The Join and Keep prefixes in the data load script can be preceded by the prefix left.

If used before Join, it specifies that the join between the two tables should be a left join. The resulting table only contains combinations between the two tables with a full data set from the first table.

If used before Keep, it specifies that the second table should be reduced to its common intersection with the first table before being stored in Qlik Sense.

Example:  

In these examples we use the source tables Table1 and Table2.

First, we perform a Left Join on the tables, resulting in VTable, containing all rows from Table1, combined with fields from matching rows in Table2.

If we perform a Left Keep instead, we will still have two tables. The two tables are associated via the common field A.

For more information, see Left.

Right

The Join and Keep prefixes in the Qlik Sense script language can be preceded by the prefix right.

If used before Join, it specifies that the join between the two tables should be a Right Join. The resulting table only contains combinations between the two tables with a full data set from the second table.

If used before Keep, it specifies that the first table should be reduced to its common intersection with the second table before being stored in Qlik Sense.

Example:  

In these examples we use the source tables Table1 and Table2.

First, we perform a Right Join on the tables, resulting in VTable, containing all rows from Table2, combined with fields from matching rows in Table1.

If we perform a Right Keep instead, we will still have two tables. The two tables are associated via the common field A.

For more information, see Right.