0 Replies Latest reply: Oct 27, 2016 12:02 PM by Jason Campbell RSS

    Populate missing data points with 0 (zero)

    Jason Campbell

      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?