Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to select BLANKS or <NULL> values

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?

15 Replies
Anonymous
Not applicable
Author

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 ?

jedgson
Creator
Creator

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.

Miguel_Angel_Baeyens

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.

Anonymous
Not applicable
Author

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

Not applicable
Author

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

C,

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.

Not applicable
Author

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.