So far, we have learned about fact tables and dimensions. We described facts as tables containing our measures and dimensions as tables containing descriptive information about our measures. However, we have described them as separate entities.

There is no data model without table linkage. The main purpose of data modeling is to create a performing set of tables -facts and dimensions- so we can join them together. 

To understand this better, think of your fact as the structural bone of your model. You load this table first. You learn that fact tables have foreign keys to your dimensions. This means that your fact table is related to your dimensions through these unique identifiers. Fact tables and dimensions are linked through joins using their table keys. 

Joins allow you to link your tables; most specifically, your facts and dimensions. Joins are statements in the script and are thus executed when the script is reloaded. In order for a join to exist, at least one table must have been loaded into memory before we use the join syntax in the script. If we don't specify to what table we want to join the next table, Qlik Sense will join it to the most recently loaded table. 

Allow me to briefly describe the logical steps of the join:

1. Load table A
2. Join statement
3. Specify to what table you want to join the next table. If not specified, use table A
4. Load table B
5. Qlik Sense then joins table A to table B through a common field name

In order for Qlik Sense to know what foreign and primary keys to use, we must have one name matching in table A and table B. If we have more than one field with the same name, Qlik Sense will create synthetic keys, which are not recommended. 

There are four types of joins in Qlik Sense:

• Outer
• Inner
• Left
• Right

An outer join will result in all possible combinations of table A and table B. No condition is applied in this join. This join is not usually used within the star schema, as it will result in unrelated data; however, an outer join might be useful to create certain types of dimensions, such us calendars. The following block code shows how to use a join. If you want to try it yourself, open the Data Load Editor in your Qlik Sense application, type in the code, and click on Load Data:

Product:
LOAD * Inline
[
ProductId, Product Code, Product Description
111119, AOCE-1247-POX, Current Account
111120, ACRW-5098-LOK, Saving Account Premium
111121, GTRD-3422-RTY, Saving Account Standard
];

Join (Product)

Country:
LOAD * Inline
[
CountryISOCode, Country
US, United States
GB, United Kingdom
];

In this example, we join Country to Product. These two tables are not related and do not have a common field name. The result will be one table with the name of the first table loaded (Product), with all possible combinations. The number of rows in the joined tables is:

# rows joined table = # of rows in [Product] table x # of rows on [Country] table

The resulting table in the Data Model Viewer looks like the following:

An inner join statement will result in a table containing records from table A matching table B. This is to say, if the value is in the first loaded table and in the second joined table, it will then be in the resulting table. This type of join is probably the most widely used.

The following code shows you the behavior of the inner join:

CustomerProducts:
LOAD * Inline
[
CustomerId, Customer Name, ProductId
1, Juan,111119
2, Martin,111119
3, Peter,111120
];

Inner Join (CustomerProducts)

Product:
LOAD * Inline
[
ProductId, Product Code, Product Description
111119, AOCE-1247-POX, Current Account
111120, ACRW-5098-LOK, Saving Account Premium
111121, GTRD-3422-RTY, Saving Account Standard
];

The resulting table will have the columns of both tables, but only those values where ProductId is the same in both tables. The common field name in this example is ProductId, and therefore, Qlik Sense will automatically join using this key field.

A left join, like the other joins, will result in one logical table. In this case, all records from the first loaded table (table A) will be kept, as well as the matching records in table B. The rest of the non-existing values in table B will be populated as null values in the resulting table. 

In this example, Juan has the product 999999. However, this product does not exist in the product table. The result will be null values for all product columns for Juan. The following code shows how to implement the left join:

CustomerProducts:
LOAD * Inline
[
CustomerId, Customer Name, ProductId
1, Juan,999999
2, Martin,111119
3, Peter,111120
];

Left Join (CustomerProducts)

Product:
LOAD * Inline
[
ProductId, Product Code, Product Description
111119, AOCE-1247-POX, Current Account
111120, ACRW-5098-LOK, Saving Account Premium
111121, GTRD-3422-RTY, Saving Account Standard
];

After running the script, the final table will look like this:

A right join is the same as a left join, however, all records in the second loaded table are kept, and the values on the first table are either joined through the common field or left null if there are no matching values.

Continuing with the same example as before, all records from the product table will be kept, and records on the first table, CustomerProducts, will be matched based on the ProductId common field, resulting in the following:

Similar to the join prefix, Qlik Sense also offers the keep prefix. This keyword can be used after left, right, or inner. Instead of joining two tables together and creating a final, logical table, the keep prefix reduces one or both tables before they are stored in Qlik Sense. These tables will show in the Data Model Viewer as two separate tables with their own names. The following code shows an example of how keep can be used:

CustomerProducts:
LOAD * Inline
[
CustomerId, Customer Name, ProductId
1, Juan,999999
2, Martin,111119
3, Peter,111120
];

Right Join (CustomerProducts)

Product:
LOAD * Inline
[
ProductId, Product Code, Product Description
111119, AOCE-1247-POX, Current Account
111120, ACRW-5098-LOK, Saving Account Premium
111121, GTRD-3422-RTY, Saving Account Standard
];