5.2 Merging Tables: Joins, Keeps & Concatenation Semantics

Merging Tables: Joins, Keeps & Concatenation Semantics

In Qlik Sense, data modeling centers on managing how tables associate, consolidate, or append within the associative engine. Data architects have three primary script primitives for combining tabular datasets: JOIN, KEEP, and CONCATENATE. Choosing the wrong consolidation mechanism can lead to catastrophic memory bloat, duplicate records, distorted aggregations, or the unintended vanishing of staging tables.

---

1. Table Consolidation Primitives: The Core Architectural Choices

When two datasets share common fields, the architect must determine how they should exist in the final data model:

                    ┌──────────────────────────────────────────────┐
                    │ How should two related tables be structured? │
                    └──────────────────────┬───────────────────────┘
                                           │
         ┌─────────────────────────────────┼─────────────────────────────────┐
         ▼                                 ▼                                 ▼
  Merge Horizontally                Filter Associatively               Append Vertically
 (Single Wider Table)             (Keep Separate Tables)             (Single Taller Table)
         │                                 │                                 │
         ▼                                 ▼                                 ▼
       JOIN                              KEEP                           CONCATENATE
(Columns Combined)               (Rows Filtered, 2 Tables)           (Rows Stacked)

• JOIN: Physically combines the columns of two tables into a single in-memory table based on matching values in common fields. The secondary table ceases to exist independently.
• KEEP: Compares common field values between two tables to filter rows (retaining matching, non-matching, or directional subsets), but leaves both tables as distinct, separate entities in the data model linked associatively.
• CONCATENATE: Physically appends the rows of one table to another vertically, unifying records under a shared column structure.

---

2. JOIN Semantics & Prefixes

A JOIN statement precedes a LOAD or SELECT statement and merges its output into another table. In Qlik Sense, joining is always evaluated on all common field names shared between the two tables.

Table A (Left):                      Table B (Right):
┌───────────┬─────────────┐          ┌───────────┬──────────────┐
│ AccountID │ CompanyName │          │ AccountID │ CreditRating │
├───────────┼─────────────┤          ├───────────┼──────────────┤
│ 101       │ Alpha Corp  │          │ 101       │ AAA          │
│ 102       │ Beta LLC    │          │ 103       │ BBB          │
└───────────┴─────────────┘          └───────────┴──────────────┘
                               │
                               ▼
             Result of LEFT JOIN (Table A) Table B:
             ┌───────────┬─────────────┬──────────────┐
             │ AccountID │ CompanyName │ CreditRating │
             ├───────────┼─────────────┼──────────────┤
             │ 101       │ Alpha Corp  │ AAA          │
             │ 102       │ Beta LLC    │ - (Null)     │
             └───────────┴─────────────┴──────────────┘

The Four JOIN Types

1. LEFT JOIN [(TargetTable)]: Preserves every record from the primary (target/left) table. If a matching key exists in the incoming (right) table, its attributes are appended. If no match exists, the new columns are populated with NULL.
2. RIGHT JOIN [(TargetTable)]: Preserves every record from the incoming (right) table. Records in the primary table that have no match in the incoming table are dropped.
3. INNER JOIN [(TargetTable)]: Preserves only records that have matching keys in both tables. Unmatched rows from both sides are discarded from the final combined table.
4. OUTER JOIN [(TargetTable)] (or unqualified JOIN): Preserves all records from both tables. Unmatched rows from either side retain their existing attributes and receive NULL for missing columns.

Specifying the Target Table

Always specify the target table explicitly in parentheses: LEFT JOIN (Fact_Sales) LOAD .... If the target table name is omitted, Qlik joins the incoming data to the immediately preceding loaded table. In complex multi-tab scripts, relying on default target resolution frequently causes data to join into the wrong table, silently corrupting the data model.

The Cartesian Product Disaster

Relational joins in Qlik require strict uniqueness on the join key in at least one of the tables. If both the left table and right table contain duplicate occurrences of the same key value, Qlik performs a many-to-many Cartesian multiplication for that key ($M \times N$ rows):

Orders Table:               OrderTags Table:
OrderID | Sales             OrderID | TagName
1001    | $50               1001    | Expedited
1001    | $50 (Split Line)  1001    | Fragile

Result of LEFT JOIN:
OrderID | Sales | TagName
1001    | $50   | Expedited
1001    | $50   | Fragile
1001    | $50   | Expedited   ◄── 4 Rows Generated! (2 x 2)
1001    | $50   | Fragile     ◄── Total Sales now sums to $200 instead of $100!

When a Cartesian join occurs in a fact table, quantitative metrics like revenue and profit are multiplied, producing invalid financial reports. If an architect must attach a single descriptive attribute from a dimension with duplicate keys, they should deduplicate the dimension or use ApplyMap() instead of JOIN.

---

3. KEEP Semantics: Associative Filtering Without Consolidation

The KEEP prefix provides the same filtering logic as JOIN, but with a fundamental architectural difference: it does not merge the two tables into one. Both tables survive the reload as independent, distinct tables in the associative data model.

Table A (Orders):                    Table B (Returns):
┌─────────┬────────┐                 ┌─────────┬──────────────┐
│ OrderID │ Amount │                 │ OrderID │ ReturnReason │
├─────────┼────────┤                 ├─────────┼──────────────┤
│ 1       │ $100   │                 │ 1       │ Damaged      │
│ 2       │ $200   │                 │ 3       │ Wrong Item   │
└─────────┴────────┘                 └─────────┴──────────────┘
                               │
                               ▼
             Result of INNER KEEP (Orders) Returns:

Table Orders (Survives):             Table Returns (Survives):
┌─────────┬────────┐                 ┌─────────┬──────────────┐
│ OrderID │ Amount │                 │ OrderID │ ReturnReason │
├─────────┼────────┤                 ├─────────┼──────────────┤
│ 1       │ $100   │                 │ 1       │ Damaged      │
└─────────┴────────┘                 └─────────┴──────────────┘
       ▲                                    ▲
       └──────── Connected via OrderID ─────┘

The Three KEEP Variants

• LEFT KEEP [(TargetTable)]: The incoming table is filtered to retain only records whose shared key values exist in the target table. The target table remains untouched. Both tables persist in the model.
• RIGHT KEEP [(TargetTable)]: The target table is filtered to retain only records matching the incoming table. Both tables persist in the model.
• INNER KEEP [(TargetTable)]: Both tables are filtered so that only records with shared keys present in both tables survive. Both tables persist in the model.

Architectural Use Cases for KEEP

1. Preserving Distinct Entity Grain: When two tables have different granularities (e.g., Customers at 1 row per customer, and Transactions at millions of rows per customer). An INNER JOIN would collapse the customer dimension into the fact table, destroying customer-only list box exploration for inactive accounts. An INNER KEEP filters both tables to active customers while keeping them separate.
2. Filtering Without Merging Columns: KEEP filters on all common field names, exactly like JOIN, but leaves both tables in the model. It does not prevent synthetic keys: if the two kept tables share more than one field, Qlik still builds a $Syn key, so rename or combine those fields first.
3. Staging Pipeline Reduction: Filtering large transaction tables against a small list of active stores before downstream transformations, without collapsing the store table into the transactions.

---

4. Concatenation Semantics: Explicit vs. Implicit

Concatenation is the vertical stacking of records. Instead of widening a table by adding columns horizontally, concatenation lengthens a table by appending rows.

Explicit Concatenation

Explicit concatenation uses the CONCATENATE [(TargetTable)] prefix before a LOAD statement. It forces the incoming rows to append directly to the target table:

Fact_Sales:
LOAD
    OrderID,
    CustomerID,
    OrderDate,
    SalesAmount
FROM [lib://QVD/CurrentYearSales.qvd] (qvd);

// Explicitly append archive records to Fact_Sales
CONCATENATE (Fact_Sales)
LOAD
    OrderID,
    CustomerID,
    OrderDate,
    SalesAmount,
    ArchiveFlag
FROM [lib://QVD/ArchiveSales.qvd] (qvd);

In explicit concatenation, if the incoming table contains fields that do not exist in the target table (such as ArchiveFlag above), Qlik automatically adds that column to the target table and populates previous rows with NULL. Conversely, fields missing in the incoming table are populated with NULL for the new rows.

Implicit (Automatic) Concatenation

Consider this real-world disaster scenario:

// Developer intends to load two distinct tables for comparison
Budget_2026:
LOAD DepartmentID, Month, TargetAmount FROM [lib://Data/Budget.qvd] (qvd);

Actuals_2026:
LOAD DepartmentID, Month, TargetAmount FROM [lib://Data/Actuals.qvd] (qvd);

Because both tables contain the exact fields DepartmentID, Month, and TargetAmount, Qlik automatically concatenates Actuals_2026 into Budget_2026. The table Actuals_2026 completely disappears from the data model! Users searching for Actuals_2026 in the sheet editor will find nothing, and budget figures will be doubled with actuals.

Preventing Automatic Concatenation: NOCONCATENATE

To prevent the QIX engine from automatically concatenating tables with identical schemas, the developer must prepend the NOCONCATENATE keyword:

Budget_2026:
LOAD DepartmentID, Month, TargetAmount FROM [lib://Data/Budget.qvd] (qvd);

// Force Qlik to create a distinct, standalone table (the label comes first, then the prefix)
Actuals_2026:
NOCONCATENATE
LOAD DepartmentID, Month, TargetAmount FROM [lib://Data/Actuals.qvd] (qvd);

With the NOCONCATENATE prefix, Qlik creates two distinct tables. Because they still share DepartmentID, Month, and TargetAmount, Qlik then links them through a synthetic key, so in a real model either rename the non-key fields (TargetAmount AS ActualAmount) or deliberately concatenate both into one fact table with a FactType flag (Section 3.2).

---

5. Comprehensive Comparison: JOIN vs. KEEP vs. CONCATENATE

---

6. Enterprise Scenario: Merging Multi-Source Sales and Claims Data

The Problem

A national health insurance provider extracts two massive transactional datasets: MedicalClaims (25 million rows) and PharmacyClaims (40 million rows). Additionally, they maintain a MemberRegistry (2 million members) and a FraudInvestigationQueue (15,000 members flagged for auditing).

The business requirements specify:

1. Medical and pharmacy claims must be analyzed together across time in unified KPI dashboard charts.
2. The fraud analytics team needs an isolated audit application that focuses exclusively on claims generated by members who appear in the FraudInvestigationQueue.
3. The member demographic attributes (RiskTier, Region) must be attached to every claim row, but inactive members who made no claims must not be added to the transaction tables.

The Architectural Implementation

// Step 1: Load the Fraud Investigation Target Members
Fraud_Queue:
LOAD
    MemberID,
    InvestigationStatus,
    InvestigatorAssigned
FROM [lib://QVD/Fraud_Registry.qvd] (qvd);

// Step 2: Use INNER KEEP to restrict claims to audited members without merging tables
Fact_MedicalClaims:
INNER KEEP (Fraud_Queue)
LOAD
    ClaimID,
    MemberID,
    ClaimDate,
    ProviderID,
    ClaimAmount,
    'Medical' as ClaimType
FROM [lib://QVD/Medical_Claims.qvd] (qvd);

// Step 3: Explicitly concatenate Pharmacy Claims into the unified claims table
CONCATENATE (Fact_MedicalClaims)
LOAD
    PharmacyClaimID as ClaimID,
    MemberID,
    DispenseDate as ClaimDate,
    PharmacyID as ProviderID,
    PrescriptionCost as ClaimAmount,
    'Pharmacy' as ClaimType
FROM [lib://QVD/Pharmacy_Claims.qvd] (qvd)
WHERE Exists(MemberID); // Prune to members already known in memory

// Step 4: Left join member attributes into the unified fact table
LEFT JOIN (Fact_MedicalClaims)
LOAD
    MemberID,
    MemberAge,
    RiskTier,
    MemberRegion
FROM [lib://QVD/Dim_Members.qvd] (qvd);

// Rename table to reflect unified architecture
RENAME TABLE Fact_MedicalClaims TO Fact_UnifiedClaims;

Architectural Evaluation

• INNER KEEP (Fraud_Queue) filtered the massive 25-million medical claims down to only the 15,000 audited members without collapsing Fraud_Queue into the claims table, allowing the fraud team to independently filter on InvestigatorAssigned.
• CONCATENATE (Fact_MedicalClaims) unified medical and pharmacy events into a single normalized fact table with standardized column names (ClaimID, ClaimDate, ClaimAmount).
• LEFT JOIN (Fact_UnifiedClaims) enriched each claim with member demographics without duplicating claims, because MemberID is guaranteed unique in Dim_Members.

---

7. Common Exam Pitfalls & Traps