QlikView Cheat Sheet

Updated 2026-04-20

Next Topic: Real-Time Business Intelligence Cheat Sheet

🧠Study flashcards on this topic124 cards · spaced repetition→

QlikView is a business intelligence and data visualization platform developed by Qlik that uses an associative in-memory data model to enable interactive analysis across multiple data sources. Unlike query-based BI tools, QlikView loads all data into RAM and maintains dynamic associations between every field, allowing users to explore data freely without predefined drill paths. Now at version 12.90 (supported through October 2026), QlikView's dual scripting capability—combining a powerful ETL scripting language with an expression-based visualization layer—makes it uniquely suited for rapid, self-service analytics where users can uncover hidden insights through ad-hoc selection and exploration.

What This Cheat Sheet Covers

This topic spans 26 focused tables and 235 indexed concepts, 124 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Load Script Prefixes and Data LoadingTable 2: Table Join and Association OperationsTable 3: Set Analysis Identifiers and OperatorsTable 4: Aggregation FunctionsTable 5: Range FunctionsTable 6: Statistical Aggregation FunctionsTable 7: String FunctionsTable 8: Date and Time FunctionsTable 9: Mathematical FunctionsTable 10: Conditional FunctionsTable 11: Logical and Null Handling FunctionsTable 12: Inter-Record FunctionsTable 13: Mapping and Lookup FunctionsTable 14: Variable Definition and ExpansionTable 15: Data Model ManagementTable 16: Chart Expression ModifiersTable 17: Ranking FunctionsTable 18: Counter Aggregation FunctionsTable 19: Dashboard Objects and VisualizationsTable 20: Selection and Field Information FunctionsTable 21: Advanced Analytics FeaturesTable 22: File and QVD Metadata FunctionsTable 23: File Formats and OptimizationTable 24: Script Control StatementsTable 25: Data Source ConnectionsTable 26: Color and Formatting Functions

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Load Script Prefixes and Data Loading

The load script is where QlikView pulls raw data in and reshapes it, and prefixes are the verbs that change how a LOAD behaves. Beyond the basic LOAD and Resident for reading files and in-memory tables, the real power lives in transformation prefixes — Crosstable unpivots, IntervalMatch bridges discrete values to ranges, and Hierarchy flattens parent-child trees. These are the building blocks every QlikView ETL script is assembled from.

Prefix	Example	Description
LOAD	`LOAD CustomerID, Sales` `FROM data.csv;`	• Loads data from files or inline definitions • the fundamental data loading statement in QlikView script
Resident	`LOAD * RESIDENT Orders` `WHERE Year=2024;`	• Loads data from an already-loaded in-memory table • used for transformations after initial load
Preceding	`LOAD Upper(Name) as Name;` `LOAD * FROM source.qvd;`	• Loads from the immediately preceding LOAD or SELECT without specifying a source • allows stacked transformations in one pass
Mapping	`Mapping LOAD OldCode,` `NewCode FROM map.csv;`	• Creates a two-column mapping table for use with ApplyMap() • table is not added to data model
Crosstable	`Crosstable(Month, Sales, 2)` `LOAD * FROM pivot.csv;`	• Unpivots crosstab data into row format • first parameter is attribute field, second is value field, third is number of qualifier columns
IntervalMatch	`IntervalMatch(EventDate)` `LOAD StartDate, EndDate` `RESIDENT Intervals;`	• Links discrete values to interval ranges • creates associations between dates and start/end periods
Generic	`Generic LOAD Key, Attr,` `Value FROM data.csv;`	• Splits key-attribute-value rows into separate tables per attribute • useful for EAV (entity-attribute-value) data
Hierarchy	`Hierarchy(NodeID, ParentID,` `Name, FullPath) LOAD *` `FROM org.csv;`	• Transforms parent-child hierarchies into expanded tables with levels and paths • creates multiple fields for hierarchy navigation
HierarchyBelongsTo	`HierarchyBelongsTo(NodeID,` `ParentID, Name, Ancestor)` `LOAD * FROM org.csv;`	• Creates ancestor-child relationship tables from hierarchies • generates all ancestor-descendant pairs for roll-up aggregations
AutoGenerate	`LOAD IterNo() as Row` `AutoGenerate(1000);`	• Generates rows without requiring external data • useful for creating calendar tables and number sequences

Table 2: Table Join and Association Operations

A defining choice in QlikView modeling is whether to physically combine tables or let the associative engine link them. JOIN and its variants merge rows into a single table, while KEEP reduces data but leaves the tables separate so the star schema stays intact — a subtle distinction that has real performance and memory consequences. Concatenate and NoConcatenate round things out by controlling when rows from different sources stack into one table.

Operation	Example	Description
JOIN	`JOIN (Orders)` `LOAD * FROM details.csv;`	• Merges tables based on common field names (outer join by default) • reduces table count but increases row duplication

The rest of this section is part of the full cheat sheet

Table 3: Set Analysis Identifiers and Operators

Set analysis is QlikView's mechanism for overriding the user's current selections inside a single expression — the way you show "this year versus all years" in the same chart. The identifiers ($ for current selections, 1 for everything, 0 for nothing) define the starting set, modifiers in angle brackets reshape it field by field, and operators like union, intersection, and exclusion combine sets. It is the single most powerful and most misunderstood feature in the expression language, so this table rewards careful study.

Element	Example	Description
$ (current)	`Sum({$} Sales)`	• Represents current user selections • default set when no set expression specified

The rest of this section is part of the full cheat sheet

Table 4: Aggregation Functions

The core summarizing functions that collapse many rows into one number across the current selection. Most are familiar — Sum, Count, Avg, Min, Max — but a few are distinctly Qlik: Only returns a value just when one distinct value remains, Concat stitches strings together, and Aggr builds an intermediate virtual table that lets you nest one aggregation inside another.

Function	Example	Description
Sum	`Sum(Sales)`	• Returns the sum of all values in an expression • most common aggregation function

The rest of this section is part of the full cheat sheet

Table 1: Load Script Prefixes and Data Loading

Prefix	Example	Description
LOAD	`LOAD CustomerID, Sales` `FROM data.csv;`	• Loads data from files or inline definitions • the fundamental data loading statement in QlikView script
Resident	`LOAD * RESIDENT Orders` `WHERE Year=2024;`	• Loads data from an already-loaded in-memory table • used for transformations after initial load
Preceding	`LOAD Upper(Name) as Name;` `LOAD * FROM source.qvd;`	• Loads from the immediately preceding LOAD or SELECT without specifying a source • allows stacked transformations in one pass
Mapping	`Mapping LOAD OldCode,` `NewCode FROM map.csv;`	• Creates a two-column mapping table for use with ApplyMap() • table is not added to data model
Crosstable	`Crosstable(Month, Sales, 2)` `LOAD * FROM pivot.csv;`	• Unpivots crosstab data into row format • first parameter is attribute field, second is value field, third is number of qualifier columns
IntervalMatch	`IntervalMatch(EventDate)` `LOAD StartDate, EndDate` `RESIDENT Intervals;`	• Links discrete values to interval ranges • creates associations between dates and start/end periods
Generic	`Generic LOAD Key, Attr,` `Value FROM data.csv;`	• Splits key-attribute-value rows into separate tables per attribute • useful for EAV (entity-attribute-value) data
Hierarchy	`Hierarchy(NodeID, ParentID,` `Name, FullPath) LOAD *` `FROM org.csv;`	• Transforms parent-child hierarchies into expanded tables with levels and paths • creates multiple fields for hierarchy navigation
HierarchyBelongsTo	`HierarchyBelongsTo(NodeID,` `ParentID, Name, Ancestor)` `LOAD * FROM org.csv;`	• Creates ancestor-child relationship tables from hierarchies • generates all ancestor-descendant pairs for roll-up aggregations
AutoGenerate	`LOAD IterNo() as Row` `AutoGenerate(1000);`	• Generates rows without requiring external data • useful for creating calendar tables and number sequences

Table 2: Table Join and Association Operations

Operation	Example	Description
JOIN	`JOIN (Orders)` `LOAD * FROM details.csv;`	• Merges tables based on common field names (outer join by default) • reduces table count but increases row duplication

Table 3: Set Analysis Identifiers and Operators

Element	Example	Description
$ (current)	`Sum({$} Sales)`	• Represents current user selections • default set when no set expression specified

Table 4: Aggregation Functions

Function	Example	Description
Sum	`Sum(Sales)`	• Returns the sum of all values in an expression • most common aggregation function