topic Re: Padding out values for pivot table to be fully expanded in QlikView

Padding out values for pivot table to be fully expanded

helen_pip — Tue, 17 Jan 2017 13:02:26 GMT

Dear Qlikview User

I have a data set loading into Qlikview from an excel spreadsheet.

The data has a Field called "Division" and there are Divisions A, B, C and D. My spreadsheet is only populated if there is a value for a Division

I.e. Date Metric Division Numertaor

Jan-17 KPI1 A 11

Jan-17 KPI1 C 13

Jan-17 KPI1 D 7

You will notice that there is no entry for Division B (Which is correct)

What I want to achieve in Qlikview is for the user to be able to choose a Division from a list box and the pivot table to still show all months across the top of the pivot table and populate any missing values with a 0.

I.e. If you were to open the attached QVW and select "Division B" from the list box, you will notice that 01/03/2016 disappears, as there is no value in the spreadsheet for Division B for this time period

Is there a clever way in Qlikview, I can always get the table to show the date field across the top of the pivot table, without having to put dummy values into Excel?

Any Help would be greatly appreciated

Kind Regards

Helen

Re: Padding out values for pivot table to be fully expanded

sunny_talwar — Tue, 17 Jan 2017 13:20:39 GMT

May be manipulation in script?

Table:

LOAD Date,

Metric,

Division,

Numerator,

AutoNumber(Date&Metric&Division) as Check

FROM

[Division.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD Distinct Date,

Metric

Resident Table;

Left Join (Temp)

LOAD Distinct Division

Resident Table;

Concatenate (Table)

LOAD Date,

Metric,

Division,

'' as Numerator

Resident Temp

Where not Exists(Check, AutoNumber(Date&Metric&Division));

DROP Table Temp;

DROP Field Check;

Re: Padding out values for pivot table to be fully expanded

helen_pip — Tue, 17 Jan 2017 13:31:18 GMT

Thanks SunnyT

This is great! I will apply this principle to my actual dataset

Thanks for your help

Helen