How to fill in missing values in data

[johanfo2]

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.

J

[#2]

Hi,

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

Best regards

@Ungvall

[johanfo2]

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.

[#4]

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

[#5]

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!!