Qlik Sense CROSSTABLE vs GENERIC LOAD: How to Transpose and Unpivot Data

This is Article 6 of the Qlik Sense Data Modeling Course.

What Can You Learn About CROSSTABLE and GENERIC LOAD in 14 Minutes?

Data often arrives in a human-readable but analysis-unfriendly “wide” format (e.g. months as columns). This guide shows you Qlik’s powerful tools for converting such data into an analysis-friendly “long” format – a process known as transposition or “unpivoting”.

1. CROSSTABLE – use it confidently to normalize Excel reports with time dimensions in the columns.
2. GENERIC LOAD – understand it to transform highly flexible EAV structures (Entity-Attribute-Value) into a clean, associative model.
3. Choose the right strategy for each use case and avoid performance pitfalls.

Time investment: 14 min reading + 1.5 hrs hands-on
Prerequisite: Data Transformation understood
Quick Win: Transpose an Excel report with CROSSTABLE in 10 minutes

How Do I Transpose an Excel Report with CROSSTABLE?

The problem: You receive a monthly sales report where each month has its own column. For Qlik, this “wide” format is inefficient – you can’t easily filter by “month” or sum values across time.

The solution with CROSSTABLE: We “melt” the month columns into two new columns: one for the attribute name (the month) and one for the value (the revenue).

Here’s how it works:

1. Identify qualifier fields: Which columns should remain as-is? In our case those are Region and Product. These are 2 qualifier fields.
2. Define attribute and value columns: We tell Qlik that the month names should go into a new column called Month and the corresponding numbers into a column called SalesAmount.
3. Apply CROSSTABLE: We place the command before the LOAD statement and specify the number of qualifier fields.

SalesTransposed:
CROSSTABLE(Month, SalesAmount, 2)
LOAD
    Region,
    Product,
    Jan,
    Feb,
    Mar,
    Apr,
    Mai,
    Jun
FROM [lib://DataFiles/monthly_sales.xlsx]
(ooxml, embedded labels, table is Sheet1);

For the full syntax details, see the Qlik CROSSTABLE prefix reference.

Parameter explanation:

• CROSSTABLE(Month, SalesAmount, 2):
  • Month: The name of the new attribute column (contains the old column names ‘Jan’, ‘Feb’, etc.).
  • SalesAmount: The name of the new value column (contains the cell values).
  • 2: The number of leading columns (Region, Product) that should not be transposed. This is the most important parameter!

Checkpoint: Do you see a table in your data model with the four columns Region, Product, Month, and SalesAmount? Perfect!

Which Transposition Method Should You Use When?

The choice of tool depends on the structure of your source data.

How Do You Normalize Excel Reports with CROSSTABLE?

CROSSTABLE is your standard tool for converting denormalized reports into a clean, Qlik-optimized structure. The goal is to turn columns that are actually data values (like months, years, or KPIs) into proper data rows.

After a CROSSTABLE transformation, a second RESIDENT load is often necessary to further process the newly created fields (e.g. converting the text “Jan_2024” into a real date).

MonthlySalesTransposed:
CROSSTABLE(MonthYear, Amount, 2)
LOAD
    Region,
    Product,
    Jan_2024,
    Feb_2024,
    Mar_2024
FROM [lib://DataFiles/monthly_sales_report.xlsx];

CleanMonthlySales:
LOAD
    Region,
    Product,
    Date(Date#(MonthYear, 'MMM_YYYY'), 'YYYY-MM') as SalesMonth,
    If(IsNum(Amount), Amount, 0) as CleanAmount
RESIDENT MonthlySalesTransposed;

DROP TABLE MonthlySalesTransposed;

How Does GENERIC LOAD Work to Convert EAV Data into Optimized Tables?

What Is an EAV Structure?

EAV stands for Entity-Attribute-Value. It is a very flexible way of storing data where the attributes can vary per entity.

• Entity: The object being described (e.g. a customer ID ‘C001’).
• Attribute: A property of the object (e.g. ‘Name’, ‘Region’, ‘Revenue’).
• Value: The value of the property (e.g. ‘John Smith’, ‘North’, ‘15000’).

A typical EAV table looks like this:

CustomerID | AttributeName | AttributeValue
C001       | Name          | Max Mustermann
C001       | Region        | Nord
C002       | Name          | Anna Schmidt
C002       | Revenue       | 25000

How Does GENERIC LOAD Compare to CROSSTABLE?

The Qlik GENERIC LOAD reference covers all syntax options. GENERIC LOAD reads an EAV structure and automatically creates a separate table for each unique attribute. These tables are linked via the key field (the entity). The result is a perfect star schema!

GENERIC LOAD
    CustomerID,
    AttributeName,
    AttributeValue
FROM customer_attributes.csv;

From the EAV table above, Qlik would automatically create the following tables, all linked via CustomerID:

• Table “Name”: Contains the fields CustomerID and Name.
• Table “Region”: Contains the fields CustomerID and Region.
• Table “Revenue”: Contains the fields CustomerID and Revenue.

Warning: Be careful if your EAV source file contains a very large number of different attributes. GENERIC will create a table for each one, which can make your data model hard to manage. It is best to pre-filter the EAV table to only the attributes you actually need.

What Is the Difference Between Manual and Automatic Transposition?

How Do You Use a Manual Pivot with CONCATENATE?

Sometimes CROSSTABLE is not flexible enough. If you have two value columns per period (e.g. Q1_Sales and Q1_Units), you need to perform the transposition manually. This is done by loading the data for each quarter separately and stacking the results with CONCATENATE.

ManualPivot:
LOAD ProductID, 'Q1' as Quarter, Q1_Sales as Sales, Q1_Units as Units
FROM quarterly_data.csv;

CONCATENATE (ManualPivot)
LOAD ProductID, 'Q2' as Quarter, Q2_Sales as Sales, Q2_Units as Units
FROM quarterly_data.csv;

CONCATENATE (ManualPivot)
LOAD ProductID, 'Q3' as Quarter, Q3_Sales as Sales, Q3_Units as Units
FROM quarterly_data.csv;

How Does Pivoting at the Chart Level Work?

Before making your script more complex, ask yourself: does the transposition really need to happen in the script? If your data is already cleanly structured (e.g. one column for date, one for revenue), you can often handle the pivoting directly in the front end using a pivot table object. This keeps your script lean and gives users maximum flexibility.

How Do I Fix Problems When Transposing Data?

Why Does CROSSTABLE Produce Unexpected Results?

Problem: After transposition, columns are missing or too many rows are created.

Cause: This is almost always due to an incorrectly set qualifier parameter (the number N). Count exactly how many columns from the left should remain as fixed columns. If this value is wrong, Qlik will try to transpose the wrong columns.

Solution: Check the number of qualifier fields in your LOAD statement and adjust the number in the CROSSTABLE command accordingly.

Why Does GENERIC LOAD Create Too Many Tables?

Problem: The script becomes slow and the data model is cluttered with hundreds of tables.

Cause: Your EAV source file contains a very large number of different, potentially irrelevant attributes.

Solution: Run a RESIDENT load before the GENERIC load and use a WHERE clause to filter down to only the attributes you need for your model. For example: WHERE WildMatch(AttributeName, '*Revenue*', '*Region*', '*Status*').

What Are the Next Steps in the Course?

Up next: Incremental Loading – Load Only Changes

Related topics:

• Data Transformation – String, Math, Date Functions
• Mapping Tables vs JOINs – Performance Optimization
• Incremental Loading – Load Only Changes

Previous: Data Transformation | Next: Incremental Loading