ExamFinch.com My Study

Study guide

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

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.4 Advanced Table Reshaping: Crosstable, Generic & Inter-Record Functions (Peek/Previous)

Key Takeaways Explain This Topic AI

The CROSSTABLE prefix normalizes wide spreadsheet matrices by unpivoting metric columns into distinct Attribute and Value rows, parameterized by the count of leading qualifier columns.

GENERIC LOAD transforms vertical Entity-Attribute-Value (EAV) data into wide horizontal dimensions by spawning multiple individual tables that must be consolidated using iterative script join loops.

Previous() inspects the field value of the immediately preceding input record from the source buffer, operating strictly on raw input rows before transformations.

Peek() retrieves field values from already-processed output records in memory, supporting arbitrary row index offsets (-1, 0, 1) and external table references.

Recursive calculations such as running totals and balance forward-filling require Peek() rather than Previous(), combined with an explicit ORDER BY clause to ensure deterministic row sequences.

Last updated: October 2026

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()

  • Technical Vector Previous() Peek()
  • Buffer Evaluated Raw Input Stream (before script logic) Processed Output Buffer (after script logic)
  • Argument Quotation Unquoted field name: Previous(Sales) Quoted string literal: Peek('Sales', ...)

Row Index Flexibility Previous input row (nest calls to go further back) Any row ( 0 , 1 , − 1 , N 0, 1, -1, N 0 , 1 , − 1 , N ) via index parameter

Cross-Table Access No (current input stream only) Yes (can query any resident table via parameter)

Recursive Calculations No (cannot see fields created in current step) Yes (enables running totals and accumulators)

Performance Profile Extremely fast pointer access Fast memory lookup; slight index overhead

Primary Use Cases Event delta tracking ( EventDate - Previous(EventDate) ) Cumulative sums, forward-filling null headers, variables

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

Important

Why RangeSum() Over Addition (+) : If Peek('RunningBalance') returns NULL (which happens on the very first row), standard addition Amount + Peek('RunningBalance') evaluates to NULL . The RangeSum() function treats nulls as zero ( 0 ), ensuring the calculation initializes seamlessly without nested If() or Alt() wrappers.

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:

Monthly Production Forecasts are supplied in wide spreadsheets with months ( M01_2026 , M02_2026 ... M12_2026 ) across columns, preventing associative modeling against actual orders.

Machine Telemetry is recorded in a dynamic vertical EAV table ( PartID , ParameterName , ParameterValue ) tracking torque, temperature, and vibration.

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

Warning

Pitfall 1: Miscounting NumHeaderCols in CROSSTABLE Misidentifying how many leading columns are static dimensions. If your spreadsheet has 3 leading attributes ( Region , Store , Department ) and you write CROSSTABLE (Month, Sales, 2) , the third column ( Department ) will be treated as the first pivoted metric column, corrupting the entire dataset. Always count qualifying header columns meticulously.

Warning

Pitfall 2: Leaving Detached GENERIC Tables in the Data Model Executing GENERIC LOAD and failing to implement the dynamic loop to consolidate the resulting tables. If the source EAV table has 150 unique parameters, Qlik creates 150 separate tables in the data model viewer, producing associative gridlock and crashing dashboard visualizations.

Warning

Pitfall 3: Attempting Recursive Accumulators with Previous() Writing Amount + Previous(RunningTotal) as RunningTotal . This fails because Previous() can only inspect raw input records from the source stream; it cannot access RunningTotal , which is an output field being constructed in the current statement. You must use Peek('RunningTotal') for recursive accumulation.

Warning

Pitfall 4: Omitting ORDER BY Before Inter-Record Processing Relying on Peek() or Previous() without sorting the data in a RESIDENT load. Relational databases and unoptimized file loads do not guarantee deterministic row order. If the table is not sorted explicitly with ORDER BY , running balances and delta calculations will be scrambled.