topic How to select BLANKS or <NULL> values in QlikView

How to select BLANKS or values

Anonymous — Thu, 02 Sep 2010 14:20:36 GMT

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?

How to select BLANKS or values

Thu, 02 Sep 2010 14:26:49 GMT

Isnull : to select blanks

NOT isnull : to select where not blank

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

How to select BLANKS or values

Anonymous — Thu, 02 Sep 2010 14:36:14 GMT

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.

How to select BLANKS or values

jedgson — Thu, 02 Sep 2010 14:52:21 GMT

using

Nul AsValue *;

In your script should work

How to select BLANKS or values

Anonymous — Thu, 02 Sep 2010 15:10:45 GMT

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 *;

How to select BLANKS or values

jedgson — Thu, 02 Sep 2010 15:14:43 GMT

Put the NullAsValue at the beginning of the script

How to select BLANKS or values

Anonymous — Fri, 03 Sep 2010 08:57:11 GMT

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

NULLASVALUE *;

Then I test DOCNUMBR with the following if statement.

(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?

How to select BLANKS or values

Miguel_Angel_Baeyens — Fri, 03 Sep 2010 09:15:18 GMT

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!

How to select BLANKS or values

jonathandienst — Fri, 03 Sep 2010 09:19:59 GMT

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

How to select BLANKS or values

Fri, 03 Sep 2010 09:53:03 GMT

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)

How to select BLANKS or values

Anonymous — Fri, 03 Sep 2010 10:20:13 GMT

Thank you all for trying to help me, but I have the idea that there is no solution for this. The Tablebox is a combination of three different tables where DOCNMBR is the keyvalue. What I try to select is a value that doesn't exist and that will not work at all ofcourse. So this is what is in my tablebox

DOCNMBR

FROM TABLE A FROM TABLE B FROM TABLE C

1 1 1
2 2
3 3 3
4 4
5
6 6 6

So if I select the empty value in row 2 I select a value that doesn't exist, so now I understand that QlikView doesn't make a selection. So I'm affraid this will never work or does any one have an idea ?

How to select BLANKS or values

jedgson — Fri, 03 Sep 2010 10:26:28 GMT

In the load script, you could map the fields from Table B and Table C into Table A. Then in the apply map statement you could specify Not Available when there is no record in Table B/C.

How to select BLANKS or values

Miguel_Angel_Baeyens — Fri, 03 Sep 2010 10:28:42 GMT

What you say is right: you cannot select a null value because it doesn't exist.

But, if I understood your question right, if you create a flag field to mark all records in the table that don't have any value will allow you to add this new field to your tablebox so you will be able now to select those records flagged (those who have one or more null fields).

Regards.

How to select BLANKS or values

Anonymous — Thu, 16 Sep 2010 10:49:12 GMT

This is how I finally make it happens. I created the expression below where [Field Name] is the column name with the NON existing values.

if (isnull([Field Name]),1,0)

Thank you all for your help

How to select BLANKS or values

Thu, 09 Feb 2012 16:35:24 GMT

One more solution, if you ask yourself against what key the value is missing then you can make a listbox with a selection expression: = if(nr<> '', if(isnull(value), 'Missing', value), value) .

Basically check if the key exists and if not then check the missing value records. Just checking the missing value records doesnt work.

the loadscript that belongs with this is

table1:

load * inline [

nr, letter

1, A

2, B

3, C

4, D

5, E

6, F ];

table2:

load * inline [

letter, value

A, 123

D, 235

F, 567 ];

A regular listbox on the value field will return nr 3, but not record nr 2 and 5 for which the letter is missing in the 2nd table.

The expression above will let you select those records with missing value in a listbox.

Another solution is of course to create a resident table which combines both and checks for missing values.

How to select BLANKS or values

Thu, 09 Feb 2012 16:41:23 GMT

Qlikview should probably just add this as an option to listboxes, give us check missing against keyfield option where you select the keyfield and then it gives the missing records that correspond to the field that the listbox is for.