Cross Table is a table with Orthogonal list of headers. Orthogonal simply means at right angles.

We come across cross tables everywhere in our daily lives.

Michael Jordan  is the greatest basketball player of our times. I am a big fan of Michael Jordan. To explain the concept of Cross table, i will take a sample of Michael Jordan’s regular season statistics.

This is a cross table because it contains 3 Orthogonal lists of headers – Season,Team and Categories.

if this is loaded in QlikView in a normal fashion, it will create separate list for each of the columns. After the data load, it will look like

If the Cross table is very large with huge number of Orthogonal headers then it will make the application very heavy because each of the column in the cross table will be loaded separately.

If you view this table in the table viewer, it will look like

In QlikView you have to load a CrossTable by using CrossTable transformation (prefix load statement with CrossTable syntax). It will then create one column for Season,One for Team ,One for each of the Category and One for Data.

Loading Data by using CrossTable transformation –

To load a crosstable, start with similar steps as loading Table file/excel file, click on “Table files”

 

Click on “Enable Transformation step” to apply  CrossTable transformation

Click on “CrossTable” under file parameters

Select the Qualifier field/s, Attribute field and Data field.

Qualifier fields are the columns to the left. You can decide which columns you want to treat as qualifiers.

Loaded Crosstable will look like the following , which is one column for Season,One for Team ,One for each of the Category and One for Data.

CrossTable Load script will look like the following. Number 2 in Crosstable syntax signifies the 2 Qualifier fields Season and Team.

After the data load, If you see the Table viewer , it will look like

Fields will be loaded in the following fashion

Please watch my video for step-by-step method of loading Cross table

 

Want to learn how cross table is loaded in Tableau, visit Cross table in Tableau