Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Analyse Excel data in Matrix (2D table) format

Dear all,

I am currently evaluating Qlik Sense and was wondering what options exist to analyse data stored in a matrix format - i.e. the cells representing the measures. Let's look at the below table:

Products purchased / UserProduct 1Product 2
User 153
User 224
User 319

This table represents the amount of products purchased by each user and is stored as an excel sheet. Data is stored in matrix/2d where each cells is actually a measure. How would I create Bar chart showing for example total products purchased by each user (total products y-axis, user x-axis) or perhaps which was the most popular product?

From what I was getting in the documentation, the excel sheets need to be normally 1 dimension (hence we call the field a dimension) and then we generate the measure ourselves as an aggregate function.

Any help would be very appreciated as this sort of data is something that my industry normally assimilates.

3 Replies
Esteemed Contributor III

Re: Analyse Excel data in Matrix (2D table) format

You have to use crosstable() to transform your data, so the will be field Product with product names as values, and there will be a measure field, where the values are stored.
Just search the forum for crosstable, you'll find examples.

Valued Contributor III

Re: Analyse Excel data in Matrix (2D table) format

You are looking for the Crosstable load functionality.

This load scrip will put product 1-3 into a product-field and the numbers of each column will fall into the amount column.


Crosstable (product, amount)


from YourTable.qvd (qvd);

Please ekskuse my Norglish and Swenglish typos.
Contributor II

Re: Analyse Excel data in Matrix (2D table) format

The Crosstable Load is also known as "unpivot" in the latest version of Qlik Sense. Check this video by mto on the subject: https://community.qlik.com/docs/DOC-17365