topic Re: Problem with Button Select In Field when data is null in QlikView

Problem with Button Select In Field when data is null

Fri, 07 Sep 2012 08:24:20 GMT

I've set up a button with a series of Select In Fields to look at all the different date fields in my data model.

I'm having the following problem. The first set of field that the button selects from are the day, month and year in the project management system that we use. If I select 5th September then the data comes back OK because I have an entry in there for that day. But if I select 6th September (I haven't made an entry for yesterday yet) then the day value comes back NULL (or '-', I know this because I've created a text object to display the value).

What this seems to mean is that when I get a NULL value QlikView just aggregates all the project management data so instead of seeing 0h 0m in the box I have set up I get 121h 48m which is everyone's total.

Can anyone explain how I can get QlikView to return 0 instead of NULL if a field has no data for a selected person?

Re: Problem with Button Select In Field when data is null

kouroshkarimi — Fri, 07 Sep 2012 08:33:36 GMT

Maybe it would be better to wrap a condition around your time box to handle Null values?

Qlikview is returning a Null because there is nothing to return, so you would have to create the zero values on the table. If you autogenerated dates with zero values on your table it might help, but it's difficult without seeing your datamodel.

Re: Problem with Button Select In Field when data is null

Fri, 07 Sep 2012 08:40:57 GMT

Hi ,

Can you provide your sample app to have better understanding on your data structure.

Regards,

Venkat

Re: Problem with Button Select In Field when data is null

Fri, 07 Sep 2012 09:28:26 GMT

Thanks for the responses. I can't really upload my report because there's some personal information in there. I guess what I maybe need to do is add some zero values to the data source where data is missing; I will maybe have to modify my data extraction job to automatically add a zero row for anyone who doesn't have any data.

Re: Problem with Button Select In Field when data is null

kouroshkarimi — Fri, 07 Sep 2012 10:10:24 GMT

I had a similar issue with missing values in a crosstable, so I created a cartesian product using the dimensions and appended the missing values to the table which fixed the issue. Maybe you could do something similar?

Re: Problem with Button Select In Field when data is null

Fri, 07 Sep 2012 10:42:20 GMT

The SQL table only contained rows for people who had actually booked time on that day, so I wrote an extra insert statement to add all the people without time booked and set their time to 0. This solved the problem. Thanks to everyone who suggested stuff.

Re: Problem with Button Select In Field when data is null

whiteline — Sat, 08 Sep 2012 12:42:23 GMT

You can't make selection of NULL value.

You can try NULLASVALUE in script to be able to select NULL as empty string.

Or you can rewrite your expressions so that the behave correctly when the date is NULL.