How to select BLANKS or <NULL> values

[Anonymous]

Hi,

I have created a tablebox which containes values like this:

fielda fieldb fieldc fieldd fielde

1 2 A B C
3 4 D E F
5 G H I
6 7 J K

etc, etc

In fieldb and fieldc there a lot of records with no value. What I want is that the user can select those BLANKS so that only the recors appears which do not have a vlaue in column fieldb or fieldc.

I have read this in other threads:

quote

Do you have a NullAsValue in your script ?

or check your NullInterpreter variable ?

unquote.

However nothing works. Can anybody help me with this?

[#2]

Isnull : to select blanks

NOT isnull : to select where not blank

Is this along the right lines.........

[Anonymous]

I'm sorry I'm not sure what you mean by that . I don't think that NOT isnull will solve my problem, because I want to see everything that ISNULL.

[jedgson]

using

Nul AsValue *;

In your script should work

[Anonymous]

I've tried something like this, but the empty one still do not shows up. Not in Customer Number nor in Not Available.

ODBC

CONNECT TO something;

[Something]:

set

LOAD

CUSTNMBR as [Custumor Number]
IF("DOCNUMBR" <> '<NULL>', "CUSTNMBR", '000000000000') as [Not Available]; NullValue='<NULL>';
NULLASVALUE *;

[jedgson]

Put the NullAsValue at the beginning of the script

[Anonymous]

Hi it still doesn't work. My very first line in the script is:

NULLASVALUE *;

Then I test DOCNUMBR with the following if statement.

IF

(IsNull("DOCNUMBR"), 'Not Available', ' ') as [Not Available];

The result is in case that the DOCNUMBR contains a value, that [Not Available] will have the value ' ' as expected.
When DOCNUMBR doesn't has a value, the result is that [Not available] has a <NULL> value. I'm expecting there the text "Not Available".

When I select in the tablebox the '' value in the [Not Available] column all rows are selected with exeption of the rows with a <NULL> value as expected.
When I try to select the <NULL> value, nothing happens at all. It seems that QlikView can't make that selection.

So, I'm still confused. Any idea's?

[Miguel_Angel_Baeyens]

Hello,

You can flag those records and then select only them. RangeNullCount returns the number of null values in the passed expressions. You only have to pass on the function all fields, creating a new field in the LOAD statement:

RangeNullCount(fielda, fieldb, fieldc, fieldd, fieldd) AS NullCount

Then you can use any where statement, conditional expression or set analysis to select only those records where NullCount is greater than 0.

Hope that helps!

[jonathandienst]

I don't think NullAsValue helps the user to select null values. Essentially you cannot select null values using a click select. You can transform the null values as you have tried, although I would probably use something like this during loading

If(IsNull(DOCNUMBR), 'Not Available', 'Available') as [IsAvailable],

Alternatively, you can use Advanced Search to find the nulls. If you select a list box and start typing, the search dialog comes up. Normally, the search string starts with ** (wildcards), but if you select a list box and type =, you can enter an advanced search expression, for example:

=IsNull(DOCNUMBR) or =Len(DOCNUMBR)=0 (these two are equivalent, in theory, but I find the second more reliable)

That will select all entries in the list box that have (or are linked to other table records that have) a null value of DOCNUMBR.

If that is too complicated for your users, you can save the advances serach in a bookmark.

Jonathan

[#10]

Hi,

i think nullasvalue or isnull solutions doesn't work for your case.Because your values are blank values.

You must try a solution like this.

if(length(FIELD)<2,'Blanks',FIELD)