I've tried something like this, but the empty one still do not shows up. Not in Customer Number nor in Not Available.
CONNECT TO something;
IF("DOCNUMBR" <> '<NULL>', "CUSTNMBR", '000000000000') as [Not Available]; NullValue='<NULL>';
Hi it still doesn't work. My very first line in the script is:
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?
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!
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.
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
FROM TABLE A FROM TABLE B FROM TABLE C
1 1 1
3 3 3
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 ?
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).
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
load * inline [
6, F ];
load * inline [
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.