Working with crosstables in the data load script

A crosstab is a common type of table featuring a matrix of values between two orthogonal lists of header data. It is usually not the optimal data format if you want to associate the data to other data tables.

This topic describes how you can unpivot a crosstab, that is, transpose parts of it into rows, using the crosstable prefix to a LOAD statement in the data load script.

Unpivoting a crosstab with one qualifying column

A crosstab is often preceded by a number of qualifying columns, which should be read in a straightforward way. In this case there is one qualifying column, Year, and a matrix of sales data per month.

If this table is simply loaded into Qlik Sense, the result will be one field for Year and one field for each of the months. This is generally not what you would like to have. You would probably prefer to have three fields generated:

• The qualifying column, in this case Year, marked with green in the table above.
• The attribute field, in this case represented by the month names Jan - Jun marked with yellow. This field can suitably be named Month.
• The data matrix values, marked with blue. In this case they represent sales data, so this can suitably be named Sales.

This can be achieved by adding the crosstable prefix to the LOAD or SELECT statement, for example:

crosstable (Month, Sales) LOAD * from ex1.xlsx;

This creates the following table in Qlik Sense:

Unpivoting a crosstab with two qualifying columns

In this case there are two qualifying columns to the left, followed by the matrix columns.

The number of qualifying columns can be stated as a third parameter to the crosstable prefix as follows:

crosstable (Month, Sales, 2) LOAD * from ex2.xlsx;

This creates the following result in Qlik Sense: