Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Pivot Table

I have two tables that are connected via a Document Number, using a Where Clause to split the field (Vehicle Document Number & Option Document Number):

Vehicle Document NumberVehicle DescriptionVolume
123Vehicle 14
1234Vehicle 24
12345Vehicle 31

Option Document NumberOption DescriptionVolume
123Option 11
123Option 22
1234Option 31
1234Option 41

I want to sum the volume for each Vehicle Description and sum the volume of every Option Description that has the same Document Number to calculate the percentage of the time each option is taken with a vehicle. Each time a vehicle is selected, I can not view the options that go with it. I am looking to create a pivot table that outputs the following:

Vehicle DescriptionOption DescriptionOption Volume/Vehicle Volume
Vehicle 1Option 125%
Option 250%
Vehicle 2Option 325%
Option 425%
2 Replies
jmvilaplanap
Valued Contributor

Re: Pivot Table

Hi,

As all the fields are equals (less one) I suggest you concatenate the tables, it will be easier to work with the data.

And work with pivot tables is trivial, just sum the values in the expression

Why don't load all the table from excel and you made two steps?

There is the QVW with a Pivot table, maybe this will be useful

Regards

Partner
Partner

Re: Pivot Table

Hi Brett,

Please find the below code as well as O/p:

VehicleInfo:

LOAD [Vehicle Document Number]  as [Document Number],

     [Vehicle Description],

     Volume as [Vehicle volume]

FROM

(biff, embedded labels, table is [Vehical Info$]);

join

Option:

LOAD [Option Document Number] as [Document Number],

     [Option Description],

     Volume as  [Option volume]

FROM

(biff, embedded labels, table is [Option info$]);

Expression:

Sum([Option volume])/Sum([Vehicle volume])

Document Number Vehicle Description Option Description Volume
29.41%
123Vehicle 1Option 250.00%
123Vehicle 1Option 125.00%
1234Vehicle 2Option 425.00%
1234Vehicle 2Option 325.00%

Save Code.png

Thanks,

Arvind Patil