5.4 Advanced Table Reshaping: Crosstable, Generic & Inter-Record Functions (Peek/Previous)

Advanced Table Reshaping: Crosstable, Generic & Inter-Record Functions (Peek/Previous)

Real-world data rarely arrives in the pristine, normalized tabular format required by the Qlik associative engine. Financial analysts maintain wide budgeting spreadsheets where time periods sprawl horizontally across dozens of columns. Healthcare and IoT systems store dynamic telemetry in vertical Entity-Attribute-Value (EAV) structures. Furthermore, business requirements frequently demand calculating running inventory balances or computing the elapsed seconds between consecutive machine events.

Mastering advanced table reshaping requires proficiency in three script mechanisms: CROSSTABLE for unpivoting wide matrices, GENERIC for pivoting EAV models, and the inter-record functions Peek() and Previous().

---

1. The CROSSTABLE Prefix: Unpivoting Wide Spreadsheets

Spreadsheets and legacy reporting exports frequently present data in cross-tabulated (pivoted) matrices, where dimensional values (such as months, quarters, or years) are formatted as separate column headers:

Raw Spreadsheet Matrix (Wide Format):
┌───────────┬──────────────┬──────────┬──────────┬──────────┐
│ AccountID │ Department   │ Jan_2026 │ Feb_2026 │ Mar_2026 │
├───────────┼──────────────┼──────────┼──────────┼──────────┤
│ 4001      │ Engineering  │ 15000    │ 18000    │ 17500    │
│ 4002      │ Marketing    │ 12000    │ 14000    │ 16000    │
└───────────┴──────────────┴──────────┴──────────┴──────────┘

Why Wide Matrices Fail in Qlik Sense

In Qlik's associative data model, having separate fields for Jan_2026, Feb_2026, and Mar_2026 is an architectural anti-pattern. It prevents users from selecting "Month" in a list box, breaks time-series line charts, and forces developers to write cumbersome master measures like Sum(Jan_2026) + Sum(Feb_2026) + Sum(Mar_2026). To enable dynamic associative analysis, the data architect must unpivot the matrix into a normalized, vertical transactional structure.

Syntax & Mechanics of CROSSTABLE

The CROSSTABLE prefix is placed immediately before a LOAD statement:

CROSSTABLE (AttributeField, ValueField [, NumHeaderCols]) LOAD ...;

• AttributeField: The name of the newly created dimensional column that will receive the unpivoted column header labels (e.g., 'Month').
• ValueField: The name of the newly created metric column that will receive the numerical cell contents (e.g., 'BudgetAmount').
• NumHeaderCols (Optional): The number of leading columns on the left that represent static qualifying dimensions (columns that should not be unpivoted). Defaults to 1 if omitted.

Result of CROSSTABLE (Month, BudgetAmount, 2):
┌───────────┬──────────────┬──────────┬──────────────┐
│ AccountID │ Department   │ Month    │ BudgetAmount │
├───────────┼──────────────┼──────────┼──────────────┤
│ 4001      │ Engineering  │ Jan_2026 │ 15000        │
│ 4001      │ Engineering  │ Feb_2026 │ 18000        │
│ 4001      │ Engineering  │ Mar_2026 │ 17500        │
│ 4002      │ Marketing    │ Jan_2026 │ 12000        │
│ 4002      │ Marketing    │ Feb_2026 │ 14000        │
│ 4002      │ Marketing    │ Mar_2026 │ 16000        │
└───────────┴──────────────┴──────────┴──────────────┘

Determining NumHeaderCols

In the example above, AccountID and Department are qualifying dimensions that define the row context. Because there are two such leading columns, NumHeaderCols must be set to 2. If NumHeaderCols were mistakenly set to 1 (or omitted), Department would be treated as the first unpivoted matrix column, corrupting both the Month dimension and the BudgetAmount measure.

---

2. The GENERIC Load Prefix: Pivoting EAV Data Models

While CROSSTABLE unpivots wide tables into tall tables, the GENERIC prefix performs the opposite operation: it pivots narrow, vertical Entity-Attribute-Value (EAV) tables into wide, normalized dimensional tables.

Raw EAV Data (Vertical):
┌──────────┬─────────────────┬───────────────┐
│ EntityID │ AttributeName   │ Value         │
├──────────┼─────────────────┼───────────────┤
│ 1001     │ BloodPressure   │ 120/80        │
│ 1001     │ HeartRate       │ 72            │
│ 1002     │ BloodPressure   │ 140/90        │
│ 1002     │ Cholesterol     │ 210           │
└──────────┴─────────────────┴───────────────┘

The Mechanics of GENERIC LOAD

An EAV schema requires three fields in the GENERIC LOAD statement:

PatientVitals:
GENERIC LOAD
    EntityID,          // Field 1: The primary key (Entity)
    AttributeName,     // Field 2: The new column headers (Attribute)
    Value              // Field 3: The cell contents (Value)
FROM [lib://Data/EAV_Vitals.qvd] (qvd);

When GENERIC LOAD executes, Qlik generates a series of separate tables—one table for each unique value found in AttributeName:

• PatientVitals.BloodPressure (EntityID, BloodPressure)
• PatientVitals.HeartRate (EntityID, HeartRate)
• PatientVitals.Cholesterol (EntityID, Cholesterol)

The Consolidation Loop Pattern

Leaving multiple generic tables detached in the data model creates a cluttered, disjointed schema. Enterprise architects always recombine the generated generic tables into a single denormalized dimension table using a standardized script loop:

// Step 1: Base table to anchor the primary keys
Dim_Patients:
LOAD DISTINCT
    EntityID
FROM [lib://Data/EAV_Vitals.qvd] (qvd);

// Step 2: Loop through all tables in memory and join matching generic tables.
// Iterate BACKWARDS: DROP TABLE renumbers the remaining tables, so a forward
// loop would skip the table that slides into the dropped table's position.
FOR i = NoOfTables() - 1 TO 0 STEP -1
    LET vTableName = TableName($(i));
    
    // Check if table name starts with the prefix 'PatientVitals.'
    IF WildMatch('$(vTableName)', 'PatientVitals.*') THEN
        LEFT JOIN (Dim_Patients)
        LOAD * RESIDENT [$(vTableName)];
        
        DROP TABLE [$(vTableName)];
    END IF
NEXT i;

This loop iterates through the data model, left-joins each generated generic attribute table back into Dim_Patients, and immediately drops the temporary generic table, leaving a clean, wide dimension table.

---

3. Inter-Record Processing: Peek() vs. Previous()

Qlik Sense evaluates script statements sequentially, row by row. To perform calculations that depend on preceding or succeeding records (such as calculating running balances, finding differences between events, or carrying down header values), Qlik provides two inter-record functions: Previous() and Peek().

                  ┌──────────────────────────────────────────────┐
                  │       Inter-Record Buffer Mechanics          │
                  └──────────────────────┬───────────────────────┘
                                         │
         ┌───────────────────────────────┴───────────────────────────────┐
         ▼                                                               ▼
    Previous()                                                         Peek()
┌────────────────────────────────────────┐     ┌────────────────────────────────────────┐
│ • Reads from RAW INPUT BUFFER          │     │ • Reads from PROCESSED OUTPUT BUFFER   │
│ • Inspects immediate previous row only │     │ • Inspects any row via index (-1, 0, N)│
│ • Cannot access newly derived fields   │     │ • Can read newly computed fields       │
│ • Cannot query other tables            │     │ • Can query external resident tables   │
└────────────────────────────────────────┘     └────────────────────────────────────────┘

1. The Previous() Function

Previous(FieldName) returns the value of FieldName from the immediately preceding input record from the source data stream.

• Input Buffer Scope: Previous() looks at data before any current-record transformations or calculations are evaluated. It can only inspect fields that physically exist in the source dataset.
• One Record per Call: Previous() returns the preceding input record. To reach further back, nest it: Previous(Previous(Amount)) returns the value two records back. It cannot look ahead or address an arbitrary row number.
• First Row Behavior: On the first row of a dataset, Previous() always returns NULL.

2. The Peek() Function

Peek('FieldName' [, RowNumber [, 'TableName']]) returns the value of FieldName from a table's output buffer (data that has already been transformed and written into memory).

• Output Buffer Scope: Peek() can inspect newly calculated fields created in the current LOAD statement, making it the only function capable of evaluating recursive self-referential calculations (like running totals).
• Relative & Absolute Indexing:
  • RowNumber = -1 (Default): Retrieves the immediately preceding output record.
  • RowNumber = 0: Retrieves the very first record processed in the table.
  • RowNumber = N: Retrieves an arbitrary 0-indexed row.
• External Table Access: Peek() can inspect records from a completely different, previously loaded table by passing 'TableName' as the third argument.

---

4. Architectural Comparison: Peek() vs. Previous()

---

5. Production Script Patterns: Real-World Use Cases

Pattern A: Calculating a Running Balance with Partition Reset

Calculating a cumulative bank account balance requires accumulating transactions over time, with the running total resetting to zero whenever the AccountID changes. This pattern requires Peek() and an explicit ORDER BY clause:

// Step 1: Stage raw transactions
Staging_Ledger:
LOAD AccountID, TransactionDate, Amount FROM [lib://Data/Ledger.qvd] (qvd);

// Step 2: Sort deterministically and calculate running balance via Peek
Fact_Ledger:
LOAD
    AccountID,
    TransactionDate,
    Amount,
    // If account changes, start fresh with Amount; otherwise accumulate
    If(AccountID = Peek('AccountID'),
        RangeSum(Amount, Peek('RunningBalance')),
        Amount
    ) as RunningBalance
RESIDENT Staging_Ledger
ORDER BY AccountID, TransactionDate ASC;

DROP TABLE Staging_Ledger;

Pattern B: Delta Between Consecutive Machine Sensor Events

Tracking machine downtime requires calculating the elapsed duration between consecutive operational status pings. Previous() is ideal for comparing consecutive raw input timestamps:

Staging_Telemetry:
LOAD MachineID, PingTimestamp, Status FROM [lib://Data/SensorLogs.qvd] (qvd);

Fact_Telemetry:
LOAD
    MachineID,
    PingTimestamp,
    Status,
    // Calculate elapsed minutes since last ping for the same machine
    If(MachineID = Previous(MachineID),
        Round((PingTimestamp - Previous(PingTimestamp)) * 1440, 0.1),
        0
    ) as ElapsedMinutesSinceLastPing
RESIDENT Staging_Telemetry
ORDER BY MachineID, PingTimestamp ASC;

DROP TABLE Staging_Telemetry;

Pattern C: Forward-Filling Missing Dimensional Headers (Carry-Down)

Financial reports frequently format headers hierarchically, leaving lower cells blank until a new category begins. Peek() can carry down the preceding valid category into subsequent rows:

Clean_Financials:
LOAD
    // If Category is blank, peek the previous row's output Category
    If(Len(Trim(RawCategory)) > 0, RawCategory, Peek('Category')) as Category,
    AccountCode,
    FinancialAmount
FROM [lib://Data/HierarchySpreadsheet.xlsx]
(ooxml, embedded labels, table is Sheet1);

---

6. Enterprise Scenario: Supply Chain Material Flow & Stock Ledger Reconstruction

The Problem

A discrete manufacturing facility tracks component parts through an assembly line. The engineering division faces three architectural bottlenecks:

1. Monthly Production Forecasts are supplied in wide spreadsheets with months (M01_2026, M02_2026 ... M12_2026) across columns, preventing associative modeling against actual orders.
2. Machine Telemetry is recorded in a dynamic vertical EAV table (PartID, ParameterName, ParameterValue) tracking torque, temperature, and vibration.
3. Part Inventory Movements must maintain an ongoing running ledger balance per warehouse bin, resetting when the bin identifier changes.

The Architectural Implementation

// Part 1: Unpivot the Wide Forecast Matrix using CROSSTABLE
Forecast_Normalized:
CROSSTABLE (ForecastMonth, ForecastQuantity, 2)
LOAD
    PartSKU,
    FacilityID,
    M01_2026, M02_2026, M03_2026, M04_2026,
    M05_2026, M06_2026, M07_2026, M08_2026,
    M09_2026, M10_2026, M11_2026, M12_2026
FROM [lib://Planning/AnnualForecast.xlsx]
(ooxml, embedded labels, table is ManufacturingPlan);

// Part 2: Pivot Telemetry via GENERIC and Consolidate
Generic_Telemetry:
GENERIC LOAD
    PartID,
    ParameterName,
    ParameterValue
FROM [lib://IoT/SensorPings.qvd] (qvd);

Dim_PartQuality:
LOAD DISTINCT PartID FROM [lib://IoT/SensorPings.qvd] (qvd);

// Iterate backwards because DROP TABLE renumbers the remaining tables
FOR i = NoOfTables() - 1 TO 0 STEP -1
    LET vTbl = TableName($(i));
    IF WildMatch('$(vTbl)', 'Generic_Telemetry.*') THEN
        LEFT JOIN (Dim_PartQuality)
        LOAD * RESIDENT [$(vTbl)];
        DROP TABLE [$(vTbl)];
    END IF
NEXT i;

// Part 3: Calculate Warehouse Bin Running Stock Ledger via Peek
Staging_BinMovements:
LOAD BinID, MovementDate, QuantityDelta FROM [lib://WMS/BinLedger.qvd] (qvd);

Fact_BinLedger:
LOAD
    BinID,
    MovementDate,
    QuantityDelta,
    If(BinID = Peek('BinID'),
        RangeSum(QuantityDelta, Peek('CurrentBinStock')),
        QuantityDelta
    ) as CurrentBinStock
RESIDENT Staging_BinMovements
ORDER BY BinID, MovementDate ASC;

DROP TABLE Staging_BinMovements;

---

7. Common Exam Pitfalls & Traps