Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filling in data gaps with zero values

Hi,

Lets say I have this data for a month:

Header 1Header 2
01/04/116
10/04/112
11/04/113
15/04/115
21/04/111

I need to fill in the gaps between so that all the dates in the month are shown, but with zero against the ones that are zero. This is because when I try to plot this data on a chart I only get 5 values, despite me having created a master calendar so that the other dates exist. I need the chart to show the continous days with zero values against the days that are zero.

Anyone help?

10 Replies
Not applicable
Author

Hi Daniel

You need a reference table with all days, and then join with your desire table, like this:

Reference_Table:

LOAD

    REF_DATE as DATE

FROM

    <your_reference_file.xls>;

Your_Table:

Inner Join(Reference_Table)

LOAD

    [Header 1] as DATE,

    [Header 2] as hValues

FROM

    <your_file.xls>;

After that:

   

change the missing values to "0", and then Supress Zero-Values and try this options:

  • ALT( hValues, 0 ) as new_hValues  or..
  • if(hValues='-',0,hValues) as new_hValues

Best Regards,

Anderson