Using the Crosstable prefix
Cross tables are a common type of table featuring a matrix of values between two orthogonal lists of header data. Whenever you have a cross table of data, you can use the Crosstable prefix to transform the data and create the desired fields.
Crosstable prefix
In the following Product table you have one column per month and one row per product.
| Product | Jan 2014 | Feb 2014 | Mar 2014 | Apr 2014 | May 2014 | Jun 2014 |
|---|---|---|---|---|---|---|
| A | 100 | 98 | 100 | 83 | 103 | 82 |
| B | 284 | 279 | 297 | 305 | 294 | 292 |
| C | 50 | 53 | 50 | 54 | 49 | 51 |
When you load the table, the output is a table with one field for Product and one field for each of the months.
Product table with Product field, and one field each for the months
If you want to analyze this data, it is much easier to have all numbers in one field and all months in another. In this case, that is a three-column table with one column for each category (Product, Month, Sales).
Product table with Product, Month, and Sales fields
The Crosstable prefix converts the data to a table with one column for Month and another for Sales. Another way to express it is to say that it takes field names and converts these to field values.
Do the following:
- Create a new app and call it Advanced Scripting Tutorial.
- Add a new script section in the Data load editor.
- Name the section Product.
-
Under DataFiles in the right menu, click Select data.
- Upload and then select Product.xlsx.
- Select the Product table in the Select data from window.
- Click Insert script.
- Click Load data.
- Open the Data model viewer. The data model looks like this:
- Click the Product tab in the Data load editor.
- Enter the following above the LOAD statement:
- Click Load data.
- Open the Data model viewer. The data model looks like this:
Your script should look like this:
Product table with Product field, and one field each for the months
CrossTable(Month, Sales)Product table with Product, Month, and Sales fields
Note that the input data typically has only one column as a qualifier field; as an internal key (Product in the above example). But you can have several. If so, all qualifying fields must be listed before the attribute fields in the LOAD statement, and the third parameter to the Crosstable prefix must be used to define the number of qualifying fields. You cannot have a preceding LOAD or a prefix in front of the Crosstable keyword. However, you can use auto-concatenate.
In a table in Qlik Sense, your data looks like this:
Table showing data loaded using Crosstable prefix
You can now, for example, create a bar chart using the data:
Bar chart showing data loaded using Crosstable prefix
Numeric interpretation will not work for the attribute fields. This means that if you have months as column headers, these will not be automatically interpreted. The work-around is to use the Crosstable prefix to create a temporary table, and to run a second pass through it to make the interpretations as shown in the following example.
Note that this is an example only. There are no accompanying exercises to be completed in Qlik Sense.
Clearing the memory cache
You can delete tables that you create to clear the memory cache. When you load into a temporary, as in the previous section, you should drop it when it is not needed anymore. For example:
You can also drop fields. For example:
As you can see, the keywords TABLE and FIELD and can be singular or plural.