topic Displaying rows with null date values in QlikView

Displaying rows with null date values

Wed, 06 Jan 2010 19:15:18 GMT

Hi,

I'm trying to make a bookmark using advanced search in order to display rows in a table with null date values.

When I set this :

= [date_test] <> ''

(the same with null()) or

=IsNull([date_test]) = 0

it works (displaying only rows without null values in this field, but when I try the opposite :

=[date_test] = ''

(the same with null())or

=IsNull([date_test]) = 1

it doesn't work. The filter doesn't give me good results. It should display rows with null values in this table.

Why ?

Displaying rows with null date values

Wed, 06 Jan 2010 20:38:59 GMT

At least as far as I know, you can't bookmark, or select, null-values because... well, they are null! There isn't anything to select.
One way of solving this is to assign a value in the load-script for the null values:
if(isnull([date_test]), 'Missing', [date_test]) as [date_test]

That way you can easily select the 'Missing' value.

/Fredrik

Displaying rows with null date values

johnw — Wed, 06 Jan 2010 21:02:38 GMT

Also worth pointing out are a couple things with your isnull() test. First, this will return 0 or -1, not 0 or 1. So you would need to test for -1 or <>0. Second, isnull() doesn't really behave in 64-bit implementations, so the preferred approach is len(...). If what you are testing is null, the len will be 0, which is false. If non-null, the len will be > 0, which is considered true, since all non-zero values are true. Or if you want to be a little more explicit and not rely on the internal implementation of true and false, use len(...)=0 and len(...)>0.

Displaying rows with null date values

Wed, 06 Jan 2010 23:13:34 GMT


fredrik.martzen wrote:
Hi
At least as far as I know, you can't bookmark, or select, null-values because... well, they are null! There isn't anything to select.
One way of solving this is to assign a value in the load-script for the null values:
if(isnull([date_test]), 'Missing', [date_test]) as [date_test]
That way you can easily select the 'Missing' value.
/Fredrik<div></div>

The row is not fully empty, there is just one field with no value inside, and I don't want to just display one field, but the entire row. In SQL or in SAS you can select a row with some field empty and there's just no problem. Weird thing I think.

Displaying rows with null date values

Wed, 06 Jan 2010 23:15:27 GMT


John Witherspoon wrote:
Also worth pointing out are a couple things with your isnull() test. First, this will return 0 or -1, not 0 or 1. So you would need to test for -1 or <>0. Second, isnull() doesn't really behave in 64-bit implementations, so the preferred approach is len(...). If what you are testing is null, the len will be 0, which is false. If non-null, the len will be > 0, which is considered true, since all non-zero values are true. Or if you want to be a little more explicit and not rely on the internal implementation of true and false, use len(...)=0 and len(...)>0. <div></div>

Sorry for "1" instead of "-1", I typed the message from home and not from work, but in my project, I tested it correctly and it didn't work.

Will try your solution tomorrow, thank you.

Displaying rows with null date values

Thu, 07 Jan 2010 09:33:57 GMT

I tried with the "len()" solution, and it still doesn't work. len() > 0 works, but not the len() = 0. It must have a solution or a setting to set, no ?