In the preceding section, Data modeling, we learned about the star schema, which includes one fact table and multiple dimension tables. But in the real word, there are situations when we need multiple fact tables in the data model. In such a situation, we can use two options to link them together. One is by using concatenation, and the other is by using the link table. If the granularity of the fact table is similar, we can use the option of concatenation, but if the granularity is not the same, we should use the link table option.

The link table concept requires three things:

• You need to create a composite key in each fact table
• You need to create a link table (which has common fields from both the fact table and composite key)
• If you have created the link table using resident load, you should delete the fields from the resident table that you have loaded in the link table

Let's understand the link table concept using an example.

Consider that the following are the tables that you want to load in the Qlik data model:

• Sales: This table contains the Country, Period, SalesPerson, ProductGroupID, and SalesAmount fields
• Budget: This table contains the Period, SalesPerson, ProductGroupID, and BudgetAmount fields
• Product: This table contains the ProductGroupID and Name fields

You can see that Sales and Budget are the fact tables and Product is a dimension table. When you load all three tables without changing the name of any fields, Qlik will generate the synthetic table, as shown in the following diagram:

As shown in the preceding diagram, Qlik has generated the synthetic table ($Syn1), which is just a logical table that shows that there are common fields between two or more tables. In our example, the fields such as ProductGroupID, Period, and SalesPerson are common in the Sales and Budget tables and thus the synthetic table has been created.

The link table also helps in removing the synthetic table and building a proper data model to get correct values. 

Now, let's see how to create a link table. The following screenshot shows the script to create a link table:

Once the preceding script is loaded, the following screenshot shows the created data model:

You can observe in the preceding data model that, this time, Qlik Sense did not create the synthetic table.