Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell474
Creator III
Creator III

Populate missing data points with 0 (zero)

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?

0 Replies