Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Joel,
If you derived Year from any Date (ex:Hire_Date) better to populate missing dates using master calendar script
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
This is exactly what I wanted to do. Thanks for your time!
Hi,
Please find the attached QVW