combine multiple dimensions in a straight table

[wanyunyang]

Hi guys,

In the script, I have a data model like:

Item, Color, Shape, Price, Qty

A, blue, round, 200, 1

B, blue, square, 200, 2

In the front end, I would like to summarize the sales by Color, Shape, Price in one straight table. Something like:

Attribute, Value, Qty

Color, blue, 3

Shape, round, 1

Shape, square, 2

Price, 200, 3

This is just a sample I created, but I have a large amount of data, so I hope the method is not too slow. Does anyone have any idea? Thanks in advance!

[SerhanKaraer]

Hello Wanyunyang,

You can use CrossTable prefix while loading the table. You have to change the order of Qty column so as to fix while unpivotting table.

crosstable(Attribute,Value,2)
LOAD Item, Qty, Color, Shape, Price INLINE [
Item, Color, Shape, Price, Qty
A, blue, round, 200, 1
B, blue, square, 200, 2
];

 I hope it solves your problem.

You can check help site for more information:

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefix...

[justISO]

Hi, to achieve this in frontend as you want, you can try something like this:

create dimension field for Attribute column ('sample_table' is your table name you are loading data from; uncheck 'Include null values' to remove null row):

=if($Table='sample_table' and $Field<>'Item' and $Field<>'Qty', $Field)

for Value column some similar dimension:

=if($Field='Shape', Shape, if($Field='Color', Color, if($Field='Price', text(Price) )))

for Qty column create measure:

=if($Field='Shape', aggr(nodistinct sum(Qty), Shape), if($Field='Color', aggr(nodistinct sum(Qty), Color), if($Field='Price', aggr(nodistinct sum(Qty), Price) )))