Qlikview Data Modeling --- Concatenation

This Blog introduces how to avoid loops and synthetic keys through Concatenation (Concatenation) fact tables:
The modeling in Qlikview is usually very simple. Generally, we only need to rename the names of some sub-segments to prevent or establish automatic association between tables.
But sometimes we can't solve the problem by simply renaming it. For example, we have multiple fact tables and multiple associations with other tables, which usually produces terrible
Synthetic key. What's more terrible is that data modeling of data loop. Qlikview cannot have data loop.
A good data model should be established in Qlikview, which is very different from the common relational database modeling. Using QLIKVIEW to create a model is similar to that of a dimension model. Based on the complexity of the data, sometimes a hybrid modeling method is required. Of course, QLIKVIEW supports such modeling.
Today we will witness how Qlikview handles such complex associations.

Concatenation of fact tables to avoid loops and synthetic keys

The following is an example:

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
];

Waste:
LOAD * INLINE [
DateID, StoreID, ProductID, WasteQty, WasteValue
1, 1, 1, 1, 10
2, 1, 2, 1, 9
1, 2, 2, 2, 17
2, 2, 2, 1, 8
];

The following data model architecture shows that qlikview automatically generates a synthetic key. the following figure shows that Sales and Waste share the same key set (StoreID, DateID, ProductID ). in this case, we can use concatenate to remove this key set.

Modify the loading method of the waste table as follows:

// Waste:
Concatenate (Sales)
LOAD * INLINE [
DateID, StoreID, ProductID, WasteQty, WasteValue
1, 1, 1, 1, 10
2, 1, 2, 1, 9
1, 2, 2, 2, 17
2, 2, 2, 1, 8
];

Reload the Script

View the attempt viewer again:

At this time, the synthetic key disappears.

At this time, the Sales table contains information about two tables, but they are in different rows.

Note:

1. When two fact tables share the same number of key set keys, concatenation should be the preferred solution to avoid qlikview automatically creating synthetic keys.

2. concatenate is equivalent to combining two small tables into a large table. This large table contains all the child segment columns and row information of two tables. Horizontal and vertical scaling is also performed.

3. QIikview directly ignores null values during computation. When a NULL row exists in the qlikview expression for calculation, qlikview directly ignores it. Other non-NULL values can be used for calculation. However, if NULLL is involved in the calculation in SQL SERVER, the result must be NULL.

There are already too many other

Qlikview Data Modeling --- Concatenation