7.1 IntervalMatch & Hierarchy Modeling (Hierarchy & HierarchyBelongsTo)

IntervalMatch & Hierarchy Modeling (Hierarchy & HierarchyBelongsTo)

In enterprise analytical data modeling, relationships between entities rarely conform entirely to simple one-to-one or one-to-many equijoins on single discrete keys. Certified Qlik Data Architects frequently encounter two complex structural patterns that break standard relational joins:

1. Continuous Range Matching: Discrete events (such as order timestamps, currency transactions, or sensor readings) that must be assigned to valid temporal or numeric windows (such as slowly changing dimension validity ranges, currency exchange rate validity periods, tax brackets, or tiered commission structures).
2. Recursive Parent-Child Hierarchies: Self-referencing tree structures (such as employee-manager organizational charts, financial charts of accounts, or multi-level bills of materials) where leaf-level transactions must roll up dynamically across arbitrary reporting levels.

To resolve these data modeling challenges without incurring severe database lock contention or Cartesian row explosions, Qlik Sense provides three dedicated script prefixes: IntervalMatch, Hierarchy, and HierarchyBelongsTo.

---

1. Mechanics of the IntervalMatch Script Prefix

In standard SQL or relational data warehouses, matching a discrete event date or numeric value into a range requires non-equijoins utilizing BETWEEN operators (e.g., WHERE OrderDate BETWEEN RateStartDate AND RateEndDate). In high-volume relational environments, non-equijoins force costly table scans and nested loops that degrade database performance.

In Qlik's in-memory associative architecture, the IntervalMatch script prefix provides an optimized, native mechanism to link discrete numeric or timestamp events into continuous closed intervals (Min <= Event <= Max).

IntervalMatch Execution Architecture:

[Discrete Events Table]                    [Interval Reference Table]
┌──────────┬─────────────┐                ┌───────────┬─────────┬─────────┐
│ OrderID  │  OrderDate  │                │ BracketID │ StartDt │  EndDt  │
├──────────┼─────────────┤                ├───────────┼─────────┼─────────┤
│ 1001     │ 2026-03-14  │                │ B1        │ 2026-01 │ 2026-03 │
│ 1002     │ 2026-05-20  │                │ B2        │ 2026-04 │ 2026-06 │
└──────────┴─────────────┘                └───────────┴─────────┴─────────┘
                 │                                     │
                 ▼                                     ▼
       ┌─────────────────────────────────────────────────────┐
       │            IntervalMatch(OrderDate) LOAD            │
       │                   StartDt, EndDt                    │
       │              RESIDENT IntervalTable;                │
       └─────────────────────────┬───────────────────────────┘
                                 │
                                 ▼
               [IntervalMatch Generated Bridge Table]
               ┌─────────────┬─────────┬─────────┐
               │  OrderDate  │ StartDt │  EndDt  │
               ├─────────────┼─────────┼─────────┤
               │ 2026-03-14  │ 2026-01 │ 2026-03 │
               │ 2026-05-20  │ 2026-04 │ 2026-06 │
               └─────────────┴─────────┴─────────┘

Standard Syntax

The standard IntervalMatch prefix evaluates a single discrete numeric event field against a table containing the lower and upper interval boundaries:

// Step 1: Load discrete transaction events
Transactions:
LOAD 
    TransactionID,
    CustomerScore,
    TransactionAmount
FROM [lib://QVD/Transactions.qvd] (qvd);

// Step 2: Load continuous interval definitions
CreditTiers:
LOAD 
    TierName,
    MinScore,
    MaxScore,
    InterestRate
FROM [lib://QVD/CreditTiers.qvd] (qvd);

// Step 3: Execute IntervalMatch to generate the associative bridge
Bridge_Scores:
IntervalMatch (CustomerScore)
LOAD 
    MinScore,
    MaxScore
RESIDENT CreditTiers;

Execution Characteristics & Closed Interval Rules

• Closed Intervals: IntervalMatch evaluates intervals as inclusive of both boundaries: MinScore <= CustomerScore <= MaxScore. If a score falls exactly on a boundary value, it is matched.
• Overlapping Intervals: If intervals overlap, a value is linked to every interval that contains it, producing one bridge row per match. After a join, overlapping ranges therefore duplicate fact rows, so check interval tables for overlaps first.
• Discrete Events Only: The event field (CustomerScore) must already exist in a previously loaded table. The interval boundaries (MinScore, MaxScore) must be loaded in the immediate LOAD statement directly following the prefix.

---

2. Extended IntervalMatch Syntax with Linking Keys

While standard IntervalMatch functions flawlessly for global thresholds (such as tax brackets or temperature limits), enterprise data models routinely require matching intervals that are partitioned by a discrete dimension key. Common examples include:

• Currency exchange rate validity windows partitioned by CurrencyCode.
• Commission tiers partitioned by SalesRepID.
• Slowly Changing Dimensions (SCD Type 2) validity dates partitioned by CustomerID or EmployeeID.

The Cartesian Explosion Trap of Standard IntervalMatch

If an architect applies standard IntervalMatch(OrderDate) against an exchange rates table containing 15 different currencies, the engine ignores currency boundaries. An order placed in Euros (EUR) on March 15 will match the March 15 date interval for EUR, USD, GBP, JPY, CAD, and every other currency in the table. This causes a massive, invalid Cartesian explosion, multiplying row counts and associating orders with incorrect foreign exchange rates.

Extended Syntax Implementation

To partition interval matching by an entity key, Qlik Sense provides the Extended IntervalMatch syntax. The entity key must be specified in the IntervalMatch parameter list and included in the LOAD statement:

// Step 1: Ingest discrete orders with transaction currency
Orders:
LOAD 
    OrderID,
    OrderDate,
    CurrencyCode,
    GrossAmount
FROM [lib://QVD/Orders.qvd] (qvd);

// Step 2: Ingest currency exchange rates with validity windows
CurrencyRates:
LOAD 
    CurrencyCode,
    ValidFromDate,
    ValidToDate,
    ExchangeRateToUSD
FROM [lib://QVD/ExchangeRates.qvd] (qvd);

// Step 3: Extended IntervalMatch partitioned by CurrencyCode
CurRatesBridge:
IntervalMatch (OrderDate, CurrencyCode)
LOAD 
    ValidFromDate,
    ValidToDate,
    CurrencyCode
RESIDENT CurrencyRates;

Architectural Mechanics of Extended Matching

When the QIX engine executes the extended syntax:

1. It partitions the intervals internally by the discrete key (CurrencyCode).
2. For each transaction row, it restricts interval evaluation strictly to the intervals matching that order's specific CurrencyCode.
3. Rows in Orders with CurrencyCode = 'EUR' are evaluated exclusively against EUR interval windows, eliminating cross-currency Cartesian multiplication.

---

3. Synthetic Key Avoidance: The Join-and-Drop Pattern

A critical trap in Qlik data modeling occurs immediately after an IntervalMatch statement executes. By default, IntervalMatch leaves a newly created bridge table in the in-memory data model.

The Triangular Association Trap

Consider the tables in memory after running the extended currency match above:

• Orders contains: OrderID, OrderDate, CurrencyCode, GrossAmount.
• CurrencyRates contains: CurrencyCode, ValidFromDate, ValidToDate, ExchangeRateToUSD.
• CurRatesBridge contains: OrderDate, CurrencyCode, ValidFromDate, ValidToDate.

Notice that CurRatesBridge shares two fields (OrderDate, CurrencyCode) with Orders, and three fields (CurrencyCode, ValidFromDate, ValidToDate) with CurrencyRates.

TRIANGULAR ASSOCIATION / SYNTHETIC KEY HAZARD:

            ┌──────────────────────┐
            │        Orders        │
            │ (OrderDate, Currency)│
            └──────────┬───────────┘
                       │
                       │ [OrderDate, CurrencyCode]
                       ▼
            ┌──────────────────────┐
            │   CurRatesBridge     │◄────────┐
            │ (IntervalMatch Table)│         │ [$Syn Key Hazard!]
            └──────────┬───────────┘         │ [ValidFrom, ValidTo, Currency]
                       │                     │
                       │ [ValidFrom, ValidTo, Currency]
                       ▼                     │
            ┌──────────────────────┐         │
            │    CurrencyRates     │─────────┘
            └──────────────────────┘

This triangular circularity forces the QIX engine to generate Synthetic Keys ($Syn), severely degrading query performance, increasing RAM consumption, and risking ambiguous calculation paths.

The Certified Production Pattern: Left Join and Drop

To eliminate synthetic keys and preserve a pristine star schema, the certified enterprise practice is to Left Join the bridge table into the fact table, then Left Join the dimension attributes, and finally Drop the intermediate tables:

// -------------------------------------------------------------------
// STEP 1: Execute Extended IntervalMatch to generate bridge
// -------------------------------------------------------------------
Temp_IntervalBridge:
IntervalMatch (OrderDate, CurrencyCode)
LOAD 
    ValidFromDate,
    ValidToDate,
    CurrencyCode
RESIDENT CurrencyRates;

// -------------------------------------------------------------------
// STEP 2: Left Join the bridge directly into the Fact Table
// -------------------------------------------------------------------
LEFT JOIN (Orders)
LOAD 
    OrderDate,
    CurrencyCode,
    ValidFromDate,
    ValidToDate
RESIDENT Temp_IntervalBridge;

// Clean up the temporary bridge table
DROP TABLE Temp_IntervalBridge;

// -------------------------------------------------------------------
// STEP 3: Left Join the Exchange Rate measure into Fact Table
// -------------------------------------------------------------------
LEFT JOIN (Orders)
LOAD 
    CurrencyCode,
    ValidFromDate,
    ValidToDate,
    ExchangeRateToUSD
RESIDENT CurrencyRates;

// -------------------------------------------------------------------
// STEP 4: Calculate Base Currency Amount and Drop Temporary Keys
// -------------------------------------------------------------------
Final_Orders:
LOAD 
    OrderID,
    OrderDate,
    CurrencyCode,
    GrossAmount,
    ExchangeRateToUSD,
    GrossAmount * ExchangeRateToUSD AS GrossAmountUSD
RESIDENT Orders;

DROP TABLE Orders;
DROP TABLE CurrencyRates;

Architectural Benefits of Join-and-Drop:

• Completely eliminates the standalone bridge table, removing synthetic keys and circular references.
• Folds the exact exchange rate directly onto each transaction record at reload time.
• Leaves a clean, single fact table in memory, maximizing front-end calculation throughput.

---

4. Hierarchy Modeling: The Hierarchy Prefix

Enterprise systems store organizational and categorical structures as recursive parent-child tables. In these tables, each row contains an entity identifier (NodeID) and a pointer to its immediate supervisor or parent (ParentID):

RECURSIVE PARENT-CHILD STRUCTURE:

NodeID   ParentID   NodeName             Title
──────   ────────   ──────────────────   ──────────────────
101      Null       Eleanor Vance        Chief Executive Officer
102      101        Marcus Brody         VP Global Sales
103      101        Sophia Patel         VP Supply Chain
104      102        David Kim            Director North America
105      104        Sarah Jenkins        Senior Account Executive

The Associative Limitation of Unflattened Trees

If this table is loaded into Qlik Sense without transformation, the QIX engine only sees immediate pairs. If a closed sale links to EmployeeID = 105 (Sarah Jenkins), selecting Marcus Brody (Node 102) in a filter pane will not associate with Sarah's sales. In the associative model, Marcus only associates with his direct reports (ParentID = 102), which is David Kim. The associative engine does not recursively traverse trees at runtime.

The Hierarchy Prefix Syntax

The Hierarchy prefix transforms a recursive parent-child table into an expanded, horizontal flat table with discrete level columns, full path strings, and depth metrics:

EmpHierarchy:
Hierarchy (
    NodeID,           // 1. Node id field (must exist in the input)
    ParentID,         // 2. Parent id field (must exist in the input)
    NodeName,         // 3. Node name field (must exist in the input)
    ParentName,       // 4. Optional: name of the new parent-name field
    NodeName,         // 5. Optional PathSource: field used to build the path (default NodeName)
    PathName,         // 6. Optional: name of the new path field
    ' / ',            // 7. Optional: path delimiter (default '/')
    NodeDepth         // 8. Optional: name of the new depth field (root = 1)
)
LOAD 
    NodeID,
    ParentID,
    NodeName,
    Title
FROM [lib://HR_Data/Employees.qvd] (qvd);

Generated Schema & Field Output

The Hierarchy prefix outputs the following transformed columns:

• NodeID, ParentID, NodeName, Title (original attributes preserved).
• ParentName: The text name of the immediate manager.
• PathName: Full breadcrumb ancestry: "Eleanor Vance / Marcus Brody / David Kim / Sarah Jenkins".
• NodeDepth: Integer representing distance from root (1 for CEO, 2 for VP, 4 for Account Exec).
• Discrete Levels: one field per level, named after the NodeName field with a number suffix (NodeName1, NodeName2, NodeName3, ... NodeNameN), up to the maximum depth of the tree.

Qlik treats every node whose ParentID is not found in the NodeID column (including a missing parent) as a root, and it loads only nodes connected to a root, which is how the prefix avoids circular references.

FLATTENED HIERARCHY TABLE OUTPUT:

NodeID  NodeName       NodeDepth  NodeName1      NodeName2     NodeName3  NodeName4      PathName
──────  ─────────────  ─────────  ─────────────  ────────────  ─────────  ─────────────  ────────────────────────
101     Eleanor Vance  1          Eleanor Vance  Null          Null       Null           Eleanor Vance
102     Marcus Brody   2          Eleanor Vance  Marcus Brody  Null       Null           Eleanor Vance / Marcus Brody
105     Sarah Jenkins  4          Eleanor Vance  Marcus Brody  David Kim  Sarah Jenkins  ... / David Kim / Sarah Jenkins

Analytical Application of Hierarchy

• Fixed Drill-Down Groups: Create dimension drill-down groups (NodeName1 -> NodeName2 -> NodeName3 -> NodeName4) in front-end bar charts or pivot tables.
• Path Breadcrumbs: Display PathName in table visualizations to clarify an employee's exact reporting hierarchy without joining multiple tables.
• Limitation: While Hierarchy creates clean level columns, it does not allow a user to select an executive in a single filter pane and aggregate all downstream leaf records without complex set analysis across all level columns.

---

5. HierarchyBelongsTo: Ancestor-Descendant Aggregation

When business requirements mandate that a user must be able to select any manager, director, division, or general ledger group in a single filter pane and immediately aggregate all transactions belonging to that node and all its direct and indirect subordinates, the architect must implement HierarchyBelongsTo.

HierarchyBelongsTo Syntax

EmpBelongsTo:
HierarchyBelongsTo (
    NodeID,           // 1. Node identifier (Primary Key)
    ParentID,         // 2. Parent identifier (Foreign Key)
    NodeName,         // 3. Node display name
    AncestorID,       // 4. Output field name for ancestor node identifier
    AncestorName,     // 5. Output field name for ancestor node name
    DepthDiff         // 6. Optional: Field name for distance between node & ancestor
)
LOAD 
    NodeID,
    ParentID,
    NodeName
FROM [lib://HR_Data/Employees.qvd] (qvd);

The Ancestor-Descendant Bridge Architecture

The HierarchyBelongsTo prefix generates a comprehensive bridge table where every node is duplicated and paired with itself and every ancestor above it all the way to the root node:

ANCESTOR-DESCENDANT BRIDGE TABLE STRUCTURE:

NodeID  NodeName       AncestorID  AncestorName   DepthDiff  Relationship Meaning
──────  ─────────────  ──────────  ─────────────  ─────────  ────────────────────────────────────
105     Sarah Jenkins  105         Sarah Jenkins  0          Self-reference (Direct)
105     Sarah Jenkins  104         David Kim      1          Direct Manager (1 level up)
105     Sarah Jenkins  102         Marcus Brody   2          Vice President (2 levels up)
105     Sarah Jenkins  101         Eleanor Vance  3          Chief Executive (Root ancestor)
104     David Kim      104         David Kim      0          Self-reference
104     David Kim      102         Marcus Brody   1          Direct Manager
104     David Kim      101         Eleanor Vance  2          Root ancestor

ASSOCIATIVE FILTER EVALUATION VIA HIERARCHYBELONGSTO:

[User Selection in UI]
Filter Pane: AncestorName = 'Marcus Brody' (Node 102)
                       │
                       ▼
[EmpBelongsTo Bridge Table]
Filters rows where AncestorID = 102:
  • NodeID 102 (Marcus Brody, DepthDiff 0)
  • NodeID 104 (David Kim, DepthDiff 1)
  • NodeID 105 (Sarah Jenkins, DepthDiff 2)
                       │
                       ▼ (QIX In-Memory Associative Propagation)
[Fact_Sales Table]
Linked via NodeID (EmployeeID):
Aggregates sales for Employee 102 + Employee 104 + Employee 105!

Why HierarchyBelongsTo Outperforms Front-End Set Analysis

• Natural Associative Rollup: Selecting an ancestor immediately isolates all descendant leaf nodes without requiring dynamic set analysis, recursive chart functions, or runtime subfield parsing.
• Ragged Hierarchies: Supports unbalanced reporting structures where different branches terminate at different depths (e.g., branch A has 3 levels, branch B has 7 levels).
• Performance: The bridge table consists entirely of compact integer symbol tokens, allowing the QIX engine to evaluate subtree aggregations at native RAM speeds.

---

6. Transformation Prefix Comparison Matrix

---

7. Enterprise Production Scenario: Global Multi-Currency E-Commerce Platform

Business Problem

A global digital retailer processes 15 million transactions annually across 12 countries. The data architecture team faces two severe modeling bottlenecks:

1. Floating Multi-Currency Transactions: Sales occur in 8 local currencies (EUR, GBP, JPY, CAD, AUD, CHF, SEK, USD). Exchange rates fluctuate daily and are published as validity date windows (ValidFrom, ValidTo) in a corporate rate table. The existing prototype used a SQL non-equijoin that took 40 minutes to extract.
2. Sales Organization Rollup: Sales representatives roll up through a 5-level geographic sales hierarchy (Sales Rep -> Branch Manager -> Regional Director -> VP Geography -> Chief Commercial Officer). When the VP of Europe selects their name in a dashboard, they must see the total converted USD sales of all 320 subordinate sales representatives, while individual reps should see only their own accounts.

Technical Architecture & Implementation

The lead architect resolved both challenges within the Tier 2 Transform layer using extended IntervalMatch and HierarchyBelongsTo:

// -------------------------------------------------------------------
// 1. INGEST FACT AND EXCHANGE RATE TABLES
// -------------------------------------------------------------------
Fact_Orders:
LOAD 
    OrderID,
    OrderDate,
    CurrencyCode,
    SalesRepID,
    LocalGrossAmount
FROM [lib://Tier1_Raw/Orders.qvd] (qvd);

CurrencyRates:
LOAD 
    CurrencyCode,
    RateStartDate,
    RateEndDate,
    ConversionRateUSD
FROM [lib://Tier1_Raw/ExchangeRates.qvd] (qvd);

// -------------------------------------------------------------------
// 2. EXTENDED INTERVALMATCH TO MAP EXCHANGE RATES
// -------------------------------------------------------------------
RateBridge:
IntervalMatch (OrderDate, CurrencyCode)
LOAD 
    RateStartDate,
    RateEndDate,
    CurrencyCode
RESIDENT CurrencyRates;

// Left Join to eliminate synthetic keys
LEFT JOIN (Fact_Orders)
LOAD 
    OrderDate,
    CurrencyCode,
    RateStartDate,
    RateEndDate
RESIDENT RateBridge;

DROP TABLE RateBridge;

LEFT JOIN (Fact_Orders)
LOAD 
    CurrencyCode,
    RateStartDate,
    RateEndDate,
    ConversionRateUSD
RESIDENT CurrencyRates;

DROP TABLE CurrencyRates;

// -------------------------------------------------------------------
// 3. TRANSFORM ORDERS WITH CONVERTED REVENUE
// -------------------------------------------------------------------
Orders_Transformed:
LOAD 
    OrderID,
    OrderDate,
    CurrencyCode,
    SalesRepID AS EmployeeID,   // same name as the hierarchy node id, so they associate
    LocalGrossAmount,
    Round(LocalGrossAmount * ConversionRateUSD, 0.01) AS ConvertedUSDAmount
RESIDENT Fact_Orders;

DROP TABLE Fact_Orders;

// -------------------------------------------------------------------
// 4. HIERARCHYBELONGSTO FOR ORGANIZATIONAL SUBTREE ROLLUP
// -------------------------------------------------------------------
SalesOrg_Raw:
LOAD 
    EmployeeID,
    ManagerID,
    EmployeeName,
    RoleTitle
FROM [lib://Tier1_Raw/SalesOrg.qvd] (qvd);

Bridge_SalesOrgRollup:
HierarchyBelongsTo (
    EmployeeID,
    ManagerID,
    EmployeeName,
    AncestorID,
    AncestorName,
    ReportingLevelDiff
)
LOAD 
    EmployeeID,
    ManagerID,
    EmployeeName
RESIDENT SalesOrg_Raw;

DROP TABLE SalesOrg_Raw;

Production Outcomes

• Reload Throughput: The extended IntervalMatch and in-memory Join-and-Drop executed across 15 million rows in 38 seconds (down from 40 minutes in relational SQL).
• Zero Synthetic Keys: Collapsing the interval bridge table left a clean star schema linking Orders_Transformed directly to Bridge_SalesOrgRollup via EmployeeID (renamed from SalesRepID).
• Executive Dashboard Functionality: Filtering on AncestorName = 'VP Europe' dynamically aggregates USD revenue across all European countries, branches, and sales reps instantly, satisfying corporate leadership requirements.

---

8. Common Exam Pitfalls & Traps