ExamFinch.com My Study

Study guide

5.2 Merging Tables: Joins, Keeps & Concatenation Semantics

Practice this exam

QSDA Qlik Sense Data Architect Study Guide Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction: About the QSDA Exam

About the QSDA Exam: Current Facts, Objectives & How to Use This Guide

2 Chapter 1: Business Requirements, Data Granularity & Security

1.1 Business Requirements, KPI Formulation & Data Granularity 1.2 Dimensionality Analysis & Slowly Changing Dimensions (SCD) 1.3 Data Security Architecture & Section Access Implementation

3 Chapter 2: Data Connectivity & Source Integration

2.1 Relational Databases, ODBC/OLE DB & Cloud Data Warehouses 2.2 REST API Connectors, Pagination Strategies & Web Data 2.3 Data Connections, lib:// Pathing & Qlik Cloud Space Governance

4 Chapter 3: Dimensional Modeling & Schema Architecture

3.1 Dimensional Modeling: Star Schema vs. Snowflake in Qlik Sense 3.2 Multi-Fact Modeling: Fact Concatenation vs. Link Tables 3.3 Resolving Synthetic Keys ($Syn) and Circular References

5 Chapter 4: QIX Engine Architecture & Key Performance Optimization

4.1 The QIX In-Memory Associative Engine: Symbol Tables & Data Tables 4.2 Composite Keys, Cardinality & AutoNumber Memory Optimization 4.3 Field Scoping, QUALIFY / UNQUALIFY & Semantic Naming

6 Chapter 5: Load Scripting Fundamentals & Table Operations

5.1 Script Structure, Preceding Load & Resident Operations 5.2 Merging Tables: Joins, Keeps & Concatenation Semantics 5.3 Mapping Tables: ApplyMap vs. Lookup & Join Performance 5.4 Advanced Table Reshaping: Crosstable, Generic & Inter-Record Functions (Peek/Previous) 5.5 Script Documentation, Organization & Reusable Include Files

7 Chapter 6: QVD Architecture, Incremental Loads & Temporal Logic

6.1 QVD File Architecture: Optimized vs. Unoptimized Loads 6.2 Multi-Tier QVD Architecture: Extract, Transform & Presentation Layers 6.3 Incremental Load Patterns: Insert, Update & Delete Strategies 6.4 Master Calendars, Canonical Dates & Temporal Logic

8 Chapter 7: Advanced Transformations, Data Cleansing & Validation

7.1 IntervalMatch & Hierarchy Modeling (Hierarchy & HierarchyBelongsTo) 7.2 Data Cleansing, Null Handling & Script Control Flow 7.3 Data Model Validation, Source Reconciliation & Subset Ratios 7.4 Script Debugging, Trace Logging & Security Auditing

5.2 Merging Tables: Joins, Keeps & Concatenation Semantics

Key Takeaways Explain This Topic AI

JOIN physically merges two tables horizontally into a single table based on shared field names, while KEEP filters records associatively but preserves both tables as distinct physical entities in the data model.

Unqualified JOIN statements default to full OUTER JOIN, whereas LEFT JOIN, RIGHT JOIN, and INNER JOIN explicitly control which table rows are preserved or discarded.

Cartesian explosions occur when joining tables across non-unique key fields, multiplying row counts geometrically and corrupting additive measure aggregations in the presentation layer.

Implicit (automatic) concatenation occurs automatically whenever two tables share an identical field list, which must be overridden with NOCONCATENATE when tables must remain separate.

Explicit CONCATENATE vertically appends rows from one table into an existing target table, inserting nulls for mismatched columns.

Last updated: October 2026

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

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 .

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.

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.

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.

Caution

The Unqualified JOIN Default Trap : In Qlik Sense script, writing JOIN LOAD ... without specifying LEFT , RIGHT , or INNER defaults to a full OUTER JOIN . This can inject unexpected null-padded rows into fact tables and cause severe memory inflation.

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 × N M \times N M × 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

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.

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.

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

Warning

The Automatic Concatenation Trap : If two tables loaded sequentially in the script possess the exact same number of fields and identical field names , the QIX engine will automatically concatenate the second table into the first, even if no CONCATENATE prefix was written!

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

Architectural Attribute JOIN KEEP CONCATENATE

Resulting Table Count Exactly 1 (secondary table is absorbed) Exactly 2 (both tables remain independent) Exactly 1 (incoming rows append to target)

Merge Orientation Horizontal (appends columns) Associative link (no physical consolidation) Vertical (appends rows)

Key Association Required Yes (merges on all common field names) Yes (filters on all common field names) No (merges schema; unmapped columns receive null)

Cartesian Multiplication Risk Severe : Multiplies rows if keys are non-unique None : Filters rows; never duplicates records None : Appends rows; never multiplies existing records

Impact on Unmatched Rows Depends on prefix (LEFT, RIGHT, INNER, OUTER) Depends on prefix (LEFT, RIGHT, INNER) All rows from both datasets are preserved

Primary Architectural Use Case Denormalizing snowflake dimensions into flat tables Filtering related tables while preserving grain Unifying multi-source transaction logs or time periods

  • 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:

Medical and pharmacy claims must be analyzed together across time in unified KPI dashboard charts.

The fraud analytics team needs an isolated audit application that focuses exclusively on claims generated by members who appear in the FraudInvestigationQueue .

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

Warning

Pitfall 1: Omitting the Target Table Name in Joins Writing LEFT JOIN LOAD ... without designating (TargetTable) . The QIX engine automatically joins the data into the immediately preceding table loaded in script. If a developer inserts a temporary calendar or lookup table above the join, the join attaches to the wrong table, causing silent data corruption.

Warning

Pitfall 2: Silent Implicit Concatenation Swallowing Staging Tables Loading two tables that have identical column names and counts without using NOCONCATENATE . The second table is invisibly merged into the first, leaving no trace in the data model viewer and breaking any subsequent scripts that reference the second table by name.

Warning

Pitfall 3: The Many-to-Many Cartesian Join Disaster Performing a LEFT JOIN where the secondary table contains non-unique keys. For every duplicate key in the secondary table, Qlik clones the primary fact row, artificially multiplying revenue, inventory, or claim metrics. Always ensure the secondary join table is deduplicated on the join key.

Warning

Pitfall 4: Confusing INNER KEEP with INNER JOIN Assuming that INNER KEEP produces a single merged table. An INNER KEEP filters both tables to their mutual intersection but keeps both tables physically separate in the data model. If your intention was to combine attributes into a single table, INNER JOIN is required.