Qlikview to fill in missing values for a dimension

[#1]

Hi Guys,

Is there any way to tell Qlikview to fill in a missing value from a dimension? Below is an example:

Year	# of People Hired
2010	3
2012	2
2010	1
2014	5
2013	1
2012	4

What if I want Qlikview to display a value of zero for the year 2011, how do I do that? Ultimately, I would want to have the following summary table:

Year	# of People Hired
2010	4
2011	0
2012	6
2013	1
2014	5

Is there any way to fill in the value for 2011?

Thanks in advance.

Joel

[qlikmsg4u]

Hi Joel,

If you derived Year from any Date (ex:Hire_Date) better to populate missing dates using master calendar script

[sushil353 · Accepted solution]

try this:

temp:
LOAD * Inline
[
Year,Hired
2010, 3
2012, 2
2010, 1
2014, 5
2013, 1
2012, 4
];


Right join (temp)
LOAD minyear + IterNo()-1 as Year
While (minyear + IterNo()-1) <= Num(maxyear);

LOAD Min(Year) as minyear,
Max(Year) as maxyear
Resident temp;


temp2:
LOAD Alt(Year,0) as y,sum(Hired) as h2
Resident temp
Group by Year;

HTH

Sushil

[#4]

This is exactly what I wanted to do. Thanks for your time!

[#5]

Hi,

Please find the attached QVW