Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Thanks SunnyT
This is great! I will apply this principle to my actual dataset
Thanks for your help
Helen