Skip to main content

Crosstable

The crosstable load prefix is used to transpose “cross table” or “pivot table” structured data. Data structured this way is commonly encountered when working with spreadsheet sources. The output and aim of the crosstable load prefix is to transpose such structures into a regular column-oriented table equivalent, as this structure is generally better suited for analysis in Qlik Sense.

Example of data structured as a crosstable and its equivalent structure after a crosstable transformation

Example that shows a cross table on the left. Table columns are Area, Lisa, James, Sharon. On the right is a table that has been transformed using a crosstable load. The table columns have been changed to Area, Sales Person, Target. The dimension data is the same in both tables.

Syntax:  

crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )

Arguments
Argument Description
attribute field name The desired output field name describing the horizontally oriented dimension that is to be transposed (the header row).
data field name

The desired output field name which describes the horizontally oriented data of the dimension that is to be transposed (the matrix of data values beneath the header row).

n

The number of qualifier fields, or unchanged dimensions, preceding the table to be transformed to generic form. The default value is 1.

This scripting function is related to the following functions:

Related functions
Function Interaction
Generic A transformation load prefix which takes an entity-attribute-value structured data set and transforms it into a regular relational table structure, separating each attribute encountered into a new field or column of data.

Example 1 – Transforming pivoted sales data (simple)

Example 2 – Transforming pivoted sales target data into a vertical table structure (intermediate)

Example 3 – Transforming pivoted sales and target data into a vertical table structure (advanced)