This is Article 12 of the Qlik Sense Data Modeling Course.
Qlik Sense Course – Article 12 of 28
← Previous Article: Fact vs Dimension – Design Decisions
→ Next Article: Implementing Slowly Changing Dimensions
What is a Link Table? A Link Table (also called Bridge Table) resolves many-to-many relationships: one customer buys multiple products, one product is bought by multiple customers. The Link Table connects both sides cleanly – without Synthetic Keys!
What Will You Learn About Link Tables for Many-to-Many Relationships in Qlik?
After this guide, you will be able to:
- Correctly model many-to-many relationships with Link Tables
- Avoid Synthetic Keys using composite keys
- Create performance-optimized Bridge Tables
Time investment: 20 min reading + 3 hrs hands-on
Prerequisites: Knowledge of Star Schema and Fact vs Dimension Design
Quick Win: In 15 minutes you will understand when and how to use Link Tables
How Do I Solve the Synthetic Key Problem with Many-to-Many in Qlik?
The Problem: You have a fact table with Sales (CustomerID, ProductID, Amount) and two dimensions (Customer, Product). Suddenly you see a $Syn table in the data model – Qlik has created a Synthetic Key! For a detailed look at how to resolve synthetic key problems, see our dedicated guide.
What happened?
When two tables share more than one common field, Qlik does not know which field should serve as the association. So it automatically creates an artificial key – the Synthetic Key.
// Example scenario
Facts_Sales: Dim_Customer:
- OrderID - CustomerID
- CustomerID ────┐ ┌──> - CustomerID
- ProductID │ │ - CustomerName
- Amount │ │
│ │ Dim_Product:
└─ $Syn ─> - ProductID
└───> - ProductName
The Solution: A Link Table with a composite key! The Link Table contains BOTH IDs as a combined key, completely avoiding Synthetic Keys.
How Do I Create My First Link Table Step by Step in Qlik?
How Do Link Tables Work for Many-to-Many Relationships in Qlik?
A customer can prefer multiple products, and a product can be preferred by multiple customers. A classic many-to-many relationship!
How Do the Dimensions Stay in Link Tables for Many-to-Many Relationships?
// Dimension: Customers
Dim_Customer:
LOAD
CustomerID,
CustomerName,
Region,
Segment
FROM [DataCustomers.xlsx]
(ooxml, embedded labels);
Explanation: The Customer dimension stays unchanged – one record per customer.
// Dimension: Products
Dim_Product:
LOAD
ProductID,
ProductName,
Category,
Brand
FROM [DataProducts.xlsx]
(ooxml, embedded labels);
Explanation: The Product dimension also stays standard – one record per product.
How Do I Create the Link Table in Qlik?
// Link Table: Customer-Product Preferences
Link_CustomerProduct:
LOAD
CustomerID & '|' & ProductID as CompositeKey, // Composite key
CustomerID, // Individual for reference
ProductID, // Individual for reference
PreferenceScore, // Attribute of the RELATIONSHIP
Date(FirstPurchaseDate) as FirstPurchaseDate, // Temporal context
TotalPurchases // Historical info
FROM [DataCustomerPreferences.xlsx]
(ooxml, embedded labels);
Explanation of the elements:
- CompositeKey: Concatenates CustomerID and ProductID with the delimiter ‘|’. So “CUST001” + “PROD123” becomes “CUST001|PROD123”
- CustomerID & ProductID individually: Enable the connection to the dimensions
- PreferenceScore, FirstPurchaseDate, TotalPurchases: These are attributes of the RELATIONSHIP between customer and product, not of the customer or product itself!
How Does the Fact Table Use a Composite Key?
// Fact Table: Sales
Facts_Sales:
LOAD
OrderID,
CustomerID & '|' & ProductID as CompositeKey, // Same key as Link Table!
Date(OrderDate) as OrderDate,
Quantity,
Amount
FROM [DataSales.xlsx]
(ooxml, embedded labels);
Explanation: The fact table creates the same CompositeKey, automatically connecting it to the Link Table.
The Result: Your data model now has a clean structure without Synthetic Keys! Customer and Product connect to the facts through the Link Table.
When Do You Need a Link Table?
Not every relationship needs a Link Table! Here is a decision guide:
When Should You Use Link Tables for Many-to-Many Relationships in Qlik?
1. True Many-to-Many
- A customer buys multiple products AND a product is bought by multiple customers
- An employee works on multiple projects AND a project has multiple employees
- A student enrolls in multiple courses AND a course has multiple students
2. Relationship-Specific Attributes
- The relationship itself has properties (e.g., “Preference Score”, “Allocation Percent”, “Role in Project”)
- These attributes belong neither to Customer nor to Product, but to the COMBINATION
When Do You NOT Need a Link Table in Qlik?
Simple 1:N (One-to-Many)
// No Link Table needed!
Facts_Sales:
LOAD
OrderID,
CustomerID, // Simple foreign key is enough
Amount
FROM Sales;
Dim_Customer:
LOAD
CustomerID,
CustomerName
FROM Customers;
Explanation: An order belongs to exactly one customer → a normal foreign key is sufficient. If your scenario doesn’t involve many-to-many relationships, a simpler JOIN or KEEP approach might be sufficient – see the comparison of JOIN alternatives to decide.
How Does the Employee-Territory Assignment Example Work in Qlik?
A more realistic scenario: sales representatives can have multiple territories, each with a different allocation.
// Dimension: Employees
Dim_Employee:
LOAD
EmployeeID,
EmployeeName,
Department,
Date(HireDate) as HireDate
FROM [DataEmployees.xlsx]
(ooxml, embedded labels);
// Dimension: Territories
Dim_Territory:
LOAD
TerritoryID,
TerritoryName,
Region,
Country
FROM [DataTerritories.xlsx]
(ooxml, embedded labels);
// Link Table: Employee-Territory Assignment with Allocation
Link_EmployeeTerritory:
LOAD
EmployeeID & '|' & TerritoryID as EmployeeTerritoryKey,
EmployeeID,
TerritoryID,
Date(AssignmentStartDate) as AssignmentStartDate,
Date(AssignmentEndDate) as AssignmentEndDate,
AllocationPercentage, // e.g. 0.6 = 60% of time
If(Len(AssignmentEndDate) = 0, 1, 0) as IsCurrentAssignment
FROM [DataEmployeeTerritoryAssignments.xlsx]
(ooxml, embedded labels);
Explanation of the key elements:
- AssignmentStartDate / EndDate: Temporal validity of the relationship
- AllocationPercentage: An employee works 60% in Territory A and 40% in Territory B
- IsCurrentAssignment: Flag for current assignments (EndDate is empty)
// Fact Table: Sales with Employee-Territory Key
Facts_Sales:
LOAD
SaleID,
EmployeeID & '|' & TerritoryID as EmployeeTerritoryKey,
Date(SaleDate) as SaleDate,
CustomerID,
ProductID,
Amount,
Quantity
FROM [DataSales.xlsx]
(ooxml, embedded labels);
Analysis Possibilities: Now you can analyze: revenue per employee, revenue per territory, workload per employee (Sum of AllocationPercentage), and performance comparisons between territories.
How Can I Use AutoNumber for Performance Optimization?
Composite keys as strings (“CUST001|PROD123”) consume more memory than necessary. The solution: AutoNumber!
// With String Composite Key (Standard)
Link_CustomerProduct:
LOAD
CustomerID & '|' & ProductID as CompositeKey, // String
CustomerID,
ProductID,
PreferenceScore
FROM Source;
// Memory: ~180 MB with 100k records
// With AutoNumber (optimized)
Link_CustomerProduct:
LOAD
AutoNumber(CustomerID & '|' & ProductID) as CompositeKey, // Integer!
CustomerID,
ProductID,
PreferenceScore
FROM Source;
Facts_Sales:
LOAD
OrderID,
AutoNumber(CustomerID & '|' & ProductID) as CompositeKey, // Same AutoNumber
Amount
FROM Sales;
// Memory: ~125 MB with 100k records
Performance Gain: 30% less memory and 20% faster associations! AutoNumber converts string keys into compact integers.
Important: Use AutoNumber for BOTH tables (Link Table AND Facts) so the mapping works correctly.
How Can You Avoid Common Mistakes with Link Tables in Qlik?
Mistake 1: Circular Reference Caused by Link Table
Symptom: Red loop marker in the data model, warning message about Circular Reference.
Cause: The Link Table connects Customer and Product, but the fact table ALSO connects them directly:
Customer ← Link_CP → Product
↓ ↓
└──────> Sales ←──────┘
= Loop!
Solution: Rename fields in Sales so they do NOT connect directly to the dimensions:
Facts_Sales:
LOAD
OrderID,
CustomerID as Sales_CustomerID, // Rename!
ProductID as Sales_ProductID, // Rename!
CustomerID & '|' & ProductID as CompositeKey, // For Link Table
Amount
FROM Sales;
Explanation: Now Sales only connects through CompositeKey to the Link Table, not directly to the dimensions.
Mistake 2: No Delimiter in the Composite Key
Symptom: Incorrect associations, some customer-product combinations are linked incorrectly.
Wrong:
CustomerID & ProductID as CompositeKey
// Problem: "12" + "34" = "1234" = "1" + "234"
// CustomerID=12, ProductID=34 has the same key as CustomerID=1, ProductID=234!
Correct:
CustomerID & '|' & ProductID as CompositeKey
// Unique: "12|34" ≠ "1|234"
Why this matters: The delimiter (e.g., ‘|’) makes the composite key unique and prevents collisions.
Mistake 3: Non-Unique Composite Key
Symptom: Duplicates in the Link Table, aggregations return incorrect values.
Diagnosis:
// Check for duplicates
CheckDuplicates:
LOAD
CompositeKey,
Count(*) as DuplicateCount
RESIDENT Link_CustomerProduct
GROUP BY CompositeKey
HAVING Count(*) > 1;
Solution 1: Extend the composite key with additional fields:
// When CustomerID + ProductID is not unique
CustomerID & '|' & ProductID & '|' & Text(Date) as CompositeKey
Solution 2: Deduplicate the Link Table:
Link_CustomerProduct:
LOAD DISTINCT
CustomerID & '|' & ProductID as CompositeKey,
CustomerID,
ProductID,
FirstSortedValue(PreferenceScore, -Date) as PreferenceScore // Latest value
FROM Source
GROUP BY CustomerID, ProductID;
How Does Revenue Allocation with Weighting Work in Qlik?
An advanced pattern: distributing revenue across multiple regions using allocation weighting.
// Fact Table: Sales
Facts_Sales:
LOAD
OrderID,
CustomerID,
ProductID,
Date(OrderDate) as OrderDate,
Revenue
FROM [DataSales.xlsx]
(ooxml, embedded labels);
// Dimension: Customers
Dim_Customer:
LOAD
CustomerID,
CustomerName,
PrimaryRegion
FROM [DataCustomers.xlsx]
(ooxml, embedded labels);
// Link Table: Customer-Region Allocation
// A multi-location customer can serve multiple regions
Link_CustomerRegion:
LOAD
CustomerID & '|' & RegionID as CustomerRegionKey,
CustomerID,
RegionID,
AllocationWeight // e.g. 0.6 = 60% of revenue to this region
FROM [DataCustomerRegionAllocation.xlsx]
(ooxml, embedded labels);
// Dimension: Regions
Dim_Region:
LOAD
RegionID,
RegionName,
Country
FROM [DataRegions.xlsx]
(ooxml, embedded labels);
Analysis expression for Revenue by Region:
// In your chart:
Sum(Revenue * AllocationWeight)
Explanation: The revenue is multiplied by the AllocationWeight. So $100 revenue from a customer with 60% Region A and 40% Region B counts as $60 for A and $40 for B.
Business Value: Perfect for multi-location customers or split attribution between sales territories!
What Are the Best Practices for Link Tables in Qlik?
Naming Convention:
- Name Link Tables with the prefix “Link_” (e.g., Link_CustomerProduct)
- Name composite keys with the suffix “Key” (e.g., CustomerProductKey, EmployeeTerritoryKey)
Composite Key:
- Always use a delimiter (‘|’ or ‘-’ or ‘_’)
- Use AutoNumber for performance in production
- Use string concatenation for development/debugging (easier to read)
Link Table Content:
- Only use for true many-to-many relationships
- Place relationship attributes in the Link Table (not in dimensions)
- Load with DISTINCT when duplicates are possible
Documentation:
- Add comments in the script: What does the Link Table resolve?
- Document which fact tables use the composite key
- Use the Data Model Viewer for verifying link tables to ensure your associations are clean
What Are the Next Steps for Link Tables in Qlik?
You can now model many-to-many relationships cleanly! Next up:
1. Temporal Dimension: What if relationships change over time? A customer was previously “Bronze”, now “Gold”. Slowly Changing Dimensions shows you how to model history.
2. Temporal Data: How do you model validity periods and point-in-time analyses? Temporal Data & IntervalMatch explains it all.
3. Resolving Synthetic Keys: More details on avoiding Synthetic Keys and Circular References: Synthetic Keys & Circular References.
For more information, see the official Qlik CONCATENATE documentation and the Qlik Community guide to link tables.
How to use link tables for many-to-many relationships in Qlik Sense?
- Star Schema in Qlik – Performance & Clarity – Foundation for clean data models
- Fact vs Dimension – Design Decisions – When does what go where?
- Synthetic Keys & Circular References – Avoiding problems
Slug: qlik-link-tables
Keywords: Qlik Link Tables, Many-to-Many Qlik, Bridge Tables Qlik, Composite Keys Qlik, avoid Synthetic Keys, Qlik data modeling, Link Table performance, AutoNumber Qlik, Junction Tables Qlik, Qlik associations
Previous: Fact vs Dimension Design | Next: Slowly Changing Dimensions