topic How to fill in missing values in data in QlikView

How to fill in missing values in data

johanfo2 — Fri, 12 Aug 2011 12:44:22 GMT

Hi,

I have run into an issue which I thought someone here could answer..

Date Name Value

-------------------------------------------------------------------

01/01/2011 John 10

01/01/2011 Peter 22

02/01/2011 John 11

*** Missing***

03/01/2011 John 10

03/01/2011 Peter 22

When I select "Peter" and graph this kind of data. I have to use "Continuous" axis in order to get all dates on the x-axis, but this introduces some visual artifacts. Instead, I'm looking for a method to "fill inn" a zero value on each date that misses a name, so that the data "is there", just with a zero value.

Optimally, this should have been solved in the raw data in the database. However, I'm not in charge of that, so that leaves me with two options:

1. Modify the data in the SQL statement

2. Modify the data in Qlikview Scripting language

Any suggestions for approaches is greatly appreciated.

How to fill in missing values in data

Fri, 12 Aug 2011 12:55:07 GMT

Hi,

You can create a calendar with all dates and use the date field instead the Date above...

Best regards

@Ungvall

How to fill in missing values in data

johanfo2 — Fri, 12 Aug 2011 13:19:02 GMT

Thanks for your reply! But it doesn't seem to cut it!

Missing dates in general is not the problem. Just looking at the date-field, I have all dates. However, some "names" does not. So when I select them, I can't easily see that a particular "name" misses a row.

I believe I need a data-modifying approch here.

How to fill in missing values in data

Fri, 12 Aug 2011 15:15:50 GMT

Ive had similar problems. I overcame them with a Cartesian / Outer join on a data set in the load script that contained an exhaustable list of dates and or names in your case. I then Concatenated this data on to my main data table and I then avoided gaps in tables and graphs etc. It might not be perfect, but it was a work around that suited me.

Regards

Moose

How to fill in missing values in data

Fri, 12 Aug 2011 23:38:38 GMT

I think you can use "Len" to count how many caracteres you have, if appears "0" you can use it as a flag.

I hope it helps!!