Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

johanfo2
Not applicable

How to fill in missing values in data

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

4 Replies
Not applicable

How to fill in missing values in data

Hi,

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

Best regards

@Ungvall

johanfo2
Not applicable

How to fill in missing values in data

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.

Not applicable

How to fill in missing values in data

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

Not applicable

How to fill in missing values in data

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