topic Re: Null Values Not Showing in QlikView

Null Values Not Showing

Mon, 24 Sep 2012 14:40:27 GMT

Hi All,

I have a question that should be obvious, but nothing seems to work. I have a straight table with the following dimensions:

Project_ID

Title

CategoryDesc

Manager

Status

=Date(Start_Time,'MM/DD/YYYY')

=Date(End_Time,'MM/DD/YYYY')

And the following expression:

=Date(End_Time,'MM/DD/YYYY')-Date(Start_Time,'MM/DD/YYYY')

However, there are values in my chart in which start time and end time are null, and these do not show up in the graph. Suppress Null values is not selected on any of the dimensions. I need all the values to show up, even when there is no start or end time. I tried this for the expression, but it did not work either:

=if(IsNull(Start_Time)=-1,'0',Date(End_Time,'MM/DD/YYYY')-Date(Start_Time,'MM/DD/YYYY'))

If I add another expression like this, all the data shows up:

=If(IsNull(Start_Time)=-1,'Null','Not Null')

Thanks for your help!

Re: Null Values Not Showing

MayilVahanan — Mon, 24 Sep 2012 14:51:00 GMT

Is for particular start time and end time have values? if both null start and end time value means?

Re: Null Values Not Showing

Mon, 24 Sep 2012 15:13:25 GMT

In most cases there is a value for both Start Time and End Time. Some have no Start Time and no End Time, and these are the items that don't show up. In the future, there may be some records with Start Times but no End Times. I will need all the records to show, regardless of whether there is a start time, end time, or neither.

Re: Null Values Not Showing

MayilVahanan — Mon, 24 Sep 2012 15:15:15 GMT

Try like this

=if(IsNull(Start_Time)=-1,'0', if(IsNull(End_Time) = -1, '0', Date(End_Time,'MM/DD/YYYY')-Date(Start_Time,'MM/DD/YYYY')))

Hope that helps

Re: Null Values Not Showing

Mon, 24 Sep 2012 15:17:41 GMT

The nulls are still now showing up. I am at a loss.

Re: Null Values Not Showing

MayilVahanan — Mon, 24 Sep 2012 15:24:19 GMT

Use len() = 0, instead of isnull. And also try like this, Date(num(End_time)-num(Start_time),'MM/DD/YYYY')

Try interval#() or interval() function too

Hope that helps

Re: Null Values Not Showing

Mon, 24 Sep 2012 15:35:11 GMT

Tried it. i wonder if there is something with the SQL load? I have attached two Excel files: one is what the data looks like when I include the nulls (using If(IsNull(start_time),'Null','Not Null')) and the other is without that extra expression.

Re: Null Values Not Showing

whiteline — Mon, 24 Sep 2012 15:41:12 GMT

Hi.

Could you provide table structure of the fields mentioned above ?

Re: Null Values Not Showing

Mon, 24 Sep 2012 15:49:52 GMT

Here is a snapshot of the table viewer:

The only data I am using is from the SysAid_Project table.

Re: Null Values Not Showing

Mon, 24 Sep 2012 15:50:37 GMT

Forgot the attachments.

Re: Null Values Not Showing

whiteline — Mon, 24 Sep 2012 15:57:48 GMT

Ok,

Have you unchecked the checkbox 'Supress Zero-Values' on Presentation tab ?

Re: Null Values Not Showing

Mon, 24 Sep 2012 16:03:04 GMT

Ha wow. That was ridiculously obvious. Thank you, whiteline and Mayil.