topic Re: Pivot Table data representation in QlikView

Pivot Table data representation

mhmhmh91 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a question about Pivot table.

I have two tables like below:

Table A:

ItemID	Group	Item Value
a100	A	100
b100	A	110
c100	B	120
d100	C	130

Table B:

Group	Group Value
A	300
B	500
C	200

The relationship between Item and Group is like Child and Parent, but Item Value and Group Value are separate and has not relationship with each other. I now want a pivot table like below (under Group Value column, leave the cell blank instead of display Group Value of the Group):

Group / Item	Item Value	Group Value
A	210	300
a100	100	-
b100	110	-
B	120	500
c100	120	-
C	130	200
d100	130	-

Re: Pivot Table data representation

rahulpawarb — Thu, 04 May 2017 04:24:09 GMT

Hello Mandi,

Trust that you are doing good!

Please follow below steps to get the desired result:

1. Add Group & ItemID in used dimension list of Pivot table.

2. In Expression tab add below expressions

//Item Value
Sum([Item Value])
//Group Value
If(Dimensionality()=1, Sum([Group Value]), Null())

* Here we are making use of Dimensionality() function to check whether row is of type detail or summary and based on it we are populating the expression value.

3. Locate the Style tab of Pivot table chart object and select Indent Mode & Use Only First Dimension Label options (While doing this provide Group / Item as label to Group dimension on Dimension tab)

Also refer the sample application attached herewith.

Hope this will be helpful.

Regards!

Rahul

Re: Pivot Table data representation

mhmhmh91 — Thu, 04 May 2017 14:13:26 GMT

It works perfectly. Thank you, Rahul!

Best,

Mandi

Re: Pivot Table data representation

rahulpawarb — Thu, 04 May 2017 16:47:15 GMT

Cheers,

Rahul