Populate missing data points with 0 (zero)

jcampbell474 — Thu, 27 Oct 2016 16:02:37 GMT

I was able to create a QV app that enables us to view facilities that are open or closed during a specified date range, thanks to Sunny T and Clever Anjos. Link to thread: https://community.qlikview.com/thread/228007

Using the same application, I need to add the number of quotes each agency produced. I thought that by creating a date value per interval, I could at least see nulls. The best I can do is see agencies that have at least one interval with a value. If’s it’s zero across date intervals, then they’re not listed (fyi - nulls and/or zero values are not suppressed). Feels like the value of 0 (zero) should be added along with the date value.

Here is a condensed version of what I’m loading right now.

Data:
LOAD
id,
YearMonth,
value

From QVD;

Agency:
LOAD
id,
agname,

OpenFiscalYearMonth,

CloseFiscalYearMonth

From QVD;

Agencies_x_:
Load
id,
(OpenFiscalYearMonth + IterNo() -1) as RefDate
Resident Agency
While IterNo() <= CloseFiscalYearMonth - OpenFiscalYearMonth + 1;

Master_Calendar:

LOAD Distinct
FiscalYearMonth as RefDate,
FiscalMonthOfYear as Month,
FiscalYear as Year,
From QVD ;

Does something need to be added to the Agencies_x_ table to populate months where no value is present? I added “0 as filler”, then in my expression, used value+filler. But, it didn’t work.

Maybe this is the wrong approach. Can anyone please tell me what I’m doing wrong of if I should just try something different?

topic Populate missing data points with 0 (zero) in QlikView

Populate missing data points with 0 (zero)