Pivot Table

[#1]

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 Number	Vehicle Description	Volume
123	Vehicle 1	4
1234	Vehicle 2	4
12345	Vehicle 3	1

Option Document Number	Option Description	Volume
123	Option 1	1
123	Option 2	2
1234	Option 3	1
1234	Option 4	1

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 Description	Option Description	Option Volume/Vehicle Volume
Vehicle 1	Option 1	25%
	Option 2	50%
Vehicle 2	Option 3	25%
	Option 4	25%

[jmvilaplanap]

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

[arvind_patil]

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%
123	Vehicle 1	Option 2	50.00%
123	Vehicle 1	Option 1	25.00%
1234	Vehicle 2	Option 4	25.00%
1234	Vehicle 2	Option 3	25.00%

Thanks,

Arvind Patil