Publications

Qlik Sense Multiple Fact Tables: Concatenate or Use Link Table?

July 2, 2026

Hector Pincheira

CIO | CTO
Digital Strategy & Technology Innovation

More than 78% of companies already use at least one BI platform. However, a huge portion of those projects run into the same silent obstacle: the moment when the model no longer consists of a single fact table. Sales, budget, inventory, collections. Four processes, four levels of granularity, a single dashboard that needs to bring them all together. Managing Qlik Sense multiple fact tables It is the ultimate test of any data architect: the decision between concatenating or building a link table defines whether your application scales with the business or collapses with it. I have seen elegant models on paper that took minutes to respond, and «ugly» models that flew. The difference was never aesthetic. It was understanding what you are asking of the associative engine. In this article, I give you the decision criteria, the complete script for each approach, and the numbers that back up each path.

QlikSense Multiple Fact Tables - Concatenate or use link table? — Multiple Fact Tables — Concatenate or use a link table?

The real problem: when the business doesn't fit in a table

Your ERP records sales by invoice and line item. The budget lives in a spreadsheet by month, salesperson, and product family. The inventory is a daily snapshot by warehouse. Three different granularities that share dimensions: customer, product, time. When you load them as is, QlikSense tries to associate them by common fields and two well-known symptoms appear: synthetic keys and circular references.

The consequences are not academic. A poorly resolved model consumes more RAM, recalculates more slowly, and—most seriously—produces inflated figures when someone casually performs a join between facts. In other words, the board of directors sees a number that does not exist. According to market data, data-driven organizations are 19 times more profitable than their peers; but that promise evaporates if the data reaching the table is duplicated by a poorly executed cross-reference.

Think of it like a port: the dimensions are the docks and each fact table is a ship with a different draft. You can merge the ships into a single one or build a central dock that connects them. Both routes work. The mistake is choosing neither and letting the engine improvise.

QlikSense multiple fact tables: the two possible paths

Faced with a QlikSense data model with multiple fact tables, there are exactly two professional architectures: the concatenated fact table and the link table. Everything else—joins between facts, tolerated synthetic keys, disconnected tables—is disguised technical debt. Let's look at each path with its complete script.

Option 1: Concatenate — a single fact table with a type flag

Qlik's general recommendation for large volumes is simple: a single concatenated fact table. All records and one field are stacked RecordType identify the origin of each row. The fields that do not apply remain null, and that is fine: nulls weigh almost nothing in Qlik's columnar model.

// ============ CONCATENATED FACT TABLE ============
Hechos:
LOAD
 FechaFactura AS cha,
 CodigoCliente AS %Cliente,
 CodigoProducto AS %Producto,
 MontoLinea AS Monto,
 Cantidad,
 'Venta' AS TipoRegistro
FROM [lib://QVD/Ventas.qvd] (qvd);
Concatenate(Hechos)
LOAD
 MakeDate(AnioPpto, MesPpto, 1) AS cha,
 CodigoVendedor AS %Vendedor,
 FamiliaProducto, // coarser granularity: family, not SKU
 MontoPresupuesto AS Monto,
 'Presupuesto' AS TipoRegistro
FROM [lib://QVD/Presupuesto.qvd] (qvd);
Concatenate(Hechos)
LOAD
 FechaFoto AS cha,
 CodigoProducto AS %Producto,
 CodigoBodega AS %Bodega,
 StockUnidades,
 CostoUnitario * StockUnidades AS ValorInventario,
 'Inventario' AS TipoRegistro
FROM [lib://QVD/StockDiario.qvd] (qvd);

Look at three decisions in the script. First, all key fields have a prefix %: a convention that distinguishes keys from attributes and prevents accidental associations. Second, each block declares its RecordType: that flag is the heart of the approach. Third, the budget comes in with its own granularity (family, month) without forcing it into the sales detail.

Then, in the frontend, each metric filters its type with Set Analysis:

// Actual sales
Sum({} Amount)
// Budget
Sum({} Amount)
// Compliance: Actual vs. Budget
Num( Sum({} Amount)
   / Sum({} Amount), '#,##0.0%')

The modifier <TipoRegistro = {'Venta'}> it tells the engine: from this single table, consider only the sales rows. Consequently, the classic «forecast versus actual» is resolved in a single chart, without joins and without the risk of duplication.

QlikSense multiple fact tables - data architecture and model performance — The model's architecture determines performance before any visual optimization.

Option 2: link table — el muelle central

When fact tables share multiple dimensions but with very different combinations, the link table keeps each fact isolated and creates a bridge table with all key combinations:

// ============ LINK TABLE MODEL ============
Ventas:
LOAD *,
 cha & '|' & %Cliente & '|' & %Producto AS %ClaveLink
FROM [lib://QVD/Ventas.qvd] (qvd);
Presupuesto:
LOAD *,
 cha & '|' & %Cliente & '|' & %Producto AS %ClaveLink
FROM [lib://QVD/Presupuesto.qvd] (qvd);
// Bridge table: unique key combinations
LinkTable:
LOAD DISTINCT %ClaveLink, cha, %Cliente, %Producto RESIDENT Ventas;
Concatenate(LinkTable)
LOAD DISTINCT %ClaveLink, cha, %Cliente, %Producto RESIDENT Presupuesto;
// Cleanup: the individual keys live only in the LinkTable
DROP FIELDS cha, %Cliente, %Producto FROM Ventas;
DROP FIELDS cha, %Cliente, %Producto FROM Presupuesto;

The composite key %KeyLink Connect each fact with the bridge, and the dimensions (calendar, customers, products) connect to the link table, not to the facts. The result is a clean model, without synthetic keys and with simple expressions in the frontend, because each table retains its fields with their proper names.

Which one to choose? The criterion in a rule

Qlik community benchmarks show a telling fact: with low-cardinality keys, the link table can be even lighter; but when key combinations reach tens of thousands, the link table consumes up to 3 times more memory than the concatenated table. Therefore, the rule I apply in real projects is this: if the facts share most of the dimensions or the volume is high, concatenate; if the granularities are very different, the facts are few, and the key cardinality is low, the link table pays off with model clarity.

💡 Key point: There is no perfect data model, there is the right model for your volume and your business question. Concatenating optimizes performance; the link table optimizes readability. To choose is to govern.

Business Impact: From Model to the Decision Table

Why should a CEO be interested in this technical discussion? Because the model defines what questions the company can answer and how quickly. With QlikSense and multiple well-integrated fact tables, «budget compliance by product family and salesperson» is just a click away—not a week-long project. Furthermore, the documented ROI of BI—an average of 127% over three years—is concentrated precisely in companies where cross-referenced data is available at the moment a decision is made, not a week later.

There is also a direct cost impact: a well-designed concatenated model reduces server RAM consumption and shortens reload windows. In a production tenant, migrating a model with three synthetic keys to a concatenated table reduced the nightly reload from 55 to 12 minutes and freed up a third of the server's memory. Less hardware, less waiting, more confidence in the figures.

Qlik Sense multiple fact tables - team analyzing data model for decisions — A well-determined model turns data cross-referencing into an immediate response, not a project.

Common Mistakes and How to Avoid Them

⚠️ Common error: Don't ignore synthetic keys "just because they work." $Syn fields are the engine that drives your model's improvisation; sooner or later, they'll take a toll on performance and produce strange results. Solution: Deliberately rename fields and decide on the architecture yourself—not the autoload.

⚠️ Common error: Join facts with JOIN. A join between sales and budget multiplies rows (the classic fan trap) and inflates totals without warning. Solution: concatenate or link table; never join between fact tables.

⚠️ Common error: forgetting the type flag when concatenating. Without RecordType, a Sum(Amount) Sum sales plus budget plus inventory into a single meaningless number. Solution: mandatory flag and master metrics with Set Analysis, never raw sums.

⚠️ Common error: Build the link table with high-cardinality keys (timestamp included in the key, for example). The bridge table explodes in size. Solution: keys at the grain level that the analysis requires, and AutoNumber() to compact them.

Best practices from experience

First, solve the architecture in the QVD layer, not in the final application: transform once, consume many times. Second, document the granularity of each concatenated block in script comments; the next developer —or you in six months— will thank you. Third, add a counter by type (Count({<TipoRegistro={'Venta'}>} cha)) and reconcile against the source after each reload: automatic auditing detects duplications before the directory does. Finally, test the model with the actual data volume, not with the development sample; cardinality is what rules.

Strategic conclusion

Data architecture decisions are business decisions disguised as technicalities. In QlikSense, multiple poorly resolved fact tables determine your response speed, infrastructure cost, and the reliability of the figure that reaches the table. My recommendation is straightforward: start by concatenating with a type flag, measure, and reserve the link table for cases where model clarity justifies its cost. The worst model is the one nobody decided on. If you want to dive deeper into how data architecture supports strategy, in hectorpincheira.com you will find more analysis for leaders who make decisions with data.