Qlikview Data Modeling --- create a Key/Link Table

In the future, we will learn how to create a Key or Link Table in QlikView to prevent fact tables loops and qlikview from automatically generating synthetic keys. When two fact tables share the same key set, concatenation should be the preferred solution to avoid qlikview automatically creating a synthetic key. however, if a fact table has more keys than another fact table, and the more key values are not associated with another fact table, at this time, it is more suitable to create a key table to connect these key values.

In this recipe, we are going to create a budget for store and product but not put any date
On this.

Store:
Load * Inline [
StoreID, StoreName
1. Store
2, Store B
];

Calendar:
Load MonthID As DateID, Month Inline [
MonthID, Month
1, Jan
2, Feb
];

Product:
Load * Inline [
ProductID, Product
1, Product
2, Product B
];

Sales:
LOAD * INLINE [
DateID, StoreID, ProductID, SaleQty, SaleValue
1, 1, 1, 2, 23
1, 1, 2, 4, 24
2, 1, 1, 4, 33
2, 1, 2, 3, 28
1, 2, 1, 2, 21
1, 2, 2, 4, 30
2, 2, 1, 3, 25
];

Budget:
LOAD * INLINE [
StoreID, ProductID, BudgetQty, BudgetValue
1, 1, 5, 50
1, 2, 6, 47
2, 1, 5, 41
2, 2, 4, 27
];

After loading the Data, view the Data Model as follows:

As shown above, QLIKVIEW automatically generates a synthetic key named $ Syn1 between Des ProductID and StoreID. the Budget and Sales tables share one $ Syn1 at the same time, but the Sales fact table has one more key named DateID than the Budget fact table. therefore, concatenate is not suitable in this case.

At this time, we can create a key field (key filed) for the Sales and Budget tables, which contains the common key fields of the two tables. As follows:

Step 1: Create a key field (key filed) for the Sales and Budget tables, which contains the common key fields of the two tables.

Sales:
LOAD
AutoNumberHash256 (StoreID, ProductID) As SalesBudgetID,
*
INLINE [
DateID, StoreID, ProductID, SaleQty, SaleValue
1, 1, 1, 2, 23
1, 1, 2, 4, 24
2, 1, 1, 4, 33
2, 1, 2, 3, 28
1, 2, 1, 2, 21
1, 2, 2, 4, 30
2, 2, 1, 3, 25
];

Budget:
LOAD
AutoNumberHash256 (StoreID, ProductID) As SalesBudgetID,
*
INLINE [
StoreID, ProductID, BudgetQty, BudgetValue
1, 1, 5, 50
1, 2, 6, 47
2, 1, 5, 41
2, 2, 4, 27
];

Step 2: Create a key table using the data in the Sales table

Key:
Load Distinct
SalesBudgetID,
StoreID,
ProductID
Resident
Sales;

Step 3: Join all matching records in the Budget table

Join (Key)
Load Distinct
SalesBudgetID,
StoreID,
ProductID
Resident
Budget;

Step 4: delete fields in the Sales and Budget tables that are already in the Key Table
// These fields are no longer needed in the fact tables
Drop Fields StoreID, ProductID From Sales;
Drop Fields StoreID, ProductID From Budget;

Step 5: Reload the script. the Data Model at this time is as follows:

Now that the synthetic key is gone.

Note:
1. The Join Load here is full outer join, which can contain all possible values in the two tables to the key table. all options in the product or store table can be correctly associated with the Sales and Budget fact tables. The distinct clause in load ensures that only unique values are added to the key table.
2. AutoNumberHash265 is an integer value that can be returned for the same combination of information. This function can be used to convert several values into an integer. Note that this function can only return the same value in the same load script. It cannot be guaranteed in different load scripts.

Zookeeper

Qlikview Data Modeling --- create a Key/Link Table