topic Re: Filling in data gaps with zero values in QlikView

Filling in data gaps with zero values

Fri, 14 Dec 2012 11:23:49 GMT

Hi,

Lets say I have this data for a month:

Header 1	Header 2
01/04/11	6
10/04/11	2
11/04/11	3
15/04/11	5
21/04/11	1

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?

Re: Filling in data gaps with zero values

Anonymous — Fri, 14 Dec 2012 11:48:41 GMT

I often have problems with this and am generally relieved when I don't as gaps in a timeseries are a massive no-no. Things you may want to try include:

1. on the date dimension, tick the 'Show all values' checkbox

2. on the presentation sheet, untick the 'Suppress zero values' and 'Suppress missing values'

If that doesn't work, I have in the past resorted to setting up dummy values of 0 in my fact tables for each combination of date and the major dimensions my charts are shown by. I hated myself for doing so and haven't had to use it recently.

Jonathan

Re: Filling in data gaps with zero values

christian77 — Fri, 14 Dec 2012 12:43:12 GMT

Create a calendar. they are many in these forums...

In your table or chart, go to Properties > Presentation > Uncheck supress cero values and check show ceros.

If your function is SUM, sum(nothing)=0, it'll show cero.

Re: Filling in data gaps with zero values

Fri, 14 Dec 2012 12:48:17 GMT

Jon, thanks for your reply.

1. The show all values checkbox kind of worked, but now the dimension axis doesn't adjust when data selections are made.

2. No effect.

How would I create dummy zero values in the fact tables? My data comes from a database so can't edit the source. My data structure is essentially this:

Master Calendar: Data:

Timestamp Timestamp Value

01/04/11 03/04/11 6

02/04/11 11/04/11 2

03/04/11

04/04/11

etc

Christian, I already have a master calendar. Not sure what you mean by sum(nothing)=0.

Thanks

Re: Filling in data gaps with zero values

thomduvi83 — Fri, 14 Dec 2012 13:02:56 GMT

Hi,

The problem is that if you have a selection, the missing dates are not associated, so will never be shown in the axis.

I've solved once this problem by adding another expression like :

Sum({1<Date={">=$(=min(Date))<=$(=max(Date))"}>}1)

And check the "Invisible" box in the Expressions tab.

Hope it helps,

Thomas

Re: Filling in data gaps with zero values

Anonymous — Fri, 14 Dec 2012 13:08:11 GMT

Nice tip Thomas. That should save me using this dummy value in the future.

Jonathan

Re: Filling in data gaps with zero values

Fri, 14 Dec 2012 13:47:00 GMT

Hi,

Thanks for your reply but adding this expression doesn't seem to do anything.

Is it possible to fill in the missing data in the script?

Thanks

Re: Filling in data gaps with zero values

thomduvi83 — Fri, 14 Dec 2012 14:43:39 GMT

1. Load distinct all the dates

2. JOIN all the dimensions (cartesian product to create every combinaison possible)

3. Create Key to the FACT if needed.

4. JOIN the resulted table to the FACT table.

That will create rows with null values for every non-existing combinaison of the key.

Then the sum on null should work and return 0.

Re: Filling in data gaps with zero values

Fri, 14 Dec 2012 15:22:27 GMT

Hi,

Any chance you could be more specific with the scripting requirements as I can't get what you've suggested to work.

I only have one dimension (TIMESTAMP) and a set of values. (Table A)

I have created a master calendar with the field TIMESTAMP which has a row for every single date. (Table B)

I have joined Table A to Table B using an outer join.

My resultant data set still only consists of the dates and values from Table A, not the missing (zero value) dates.

Re: Filling in data gaps with zero values

thomduvi83 — Fri, 14 Dec 2012 16:07:31 GMT

That should work...

Have a look at this small sample.

Don't forget to uncheck "Suppress zero values" in your graph

Re: Filling in data gaps with zero values

Sun, 08 Dec 2013 20:42:24 GMT

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