
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Select blank in drop down list qlikview
Hi There
I have created chart table and showing multiple column data. Users seeing blank records as '-'.
I want to allow users to select '-' when they click on drop down of selected column.
Any suggestions?
Thanks in advance,
- Tags:
- new_to_qlikview
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Normally '-' is a NULL value, and NULL values can't be selected because it does not exist.
What you could use is a check when loading data like IF(LEN(Field)=0, 'empty' Field) AS Field.
Also you could use is NullAsValue (see the helpfile https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ScriptRegularStatement...)
NullAsValue
The NullAsValue statement specifies for which fields that NULL should be converted to a value.
Syntax:
NullAsValue *fieldlist
By default, QlikView considers NULL values to be missing or undefined entities. However, certain database contexts imply that NULL values are to be considered as special values rather than simply missing values. The fact that NULL values are normally not allowed to link to other NULL values can be suspended by means of the NullAsValue statement.
The NullAsValue statement operates as a switch and will operate on subsequent loading statements. It can be switched off again by means of the NullAsNull statement.
Arguments:
Argument | Description |
---|---|
*fieldlist | A comma separated list of the fields for which NullAsValue should be turned on. Using * as field list indicates all fields. The wildcard characters * and ? are allowed in field names. Quoting of field names may be necessary when wildcards are used. |
Example:
NullAsValue A,B;
Set NullValue = 'NULL';
LOAD A,B from x.csv;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
'-' means a NULL value. NULL values cannot be selected in QlikView because they actually represent ... nothing.
You'll have to replace '-' with a value. For fields, you can detect NULL values and replace them with an instantly recognisable string like '*UNDEFINED*' or 'UNKNOWN' in your load script.
Best,
Peter

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Normally '-' is a NULL value, and NULL values can't be selected because it does not exist.
What you could use is a check when loading data like IF(LEN(Field)=0, 'empty' Field) AS Field.
Also you could use is NullAsValue (see the helpfile https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ScriptRegularStatement...)
NullAsValue
The NullAsValue statement specifies for which fields that NULL should be converted to a value.
Syntax:
NullAsValue *fieldlist
By default, QlikView considers NULL values to be missing or undefined entities. However, certain database contexts imply that NULL values are to be considered as special values rather than simply missing values. The fact that NULL values are normally not allowed to link to other NULL values can be suspended by means of the NullAsValue statement.
The NullAsValue statement operates as a switch and will operate on subsequent loading statements. It can be switched off again by means of the NullAsNull statement.
Arguments:
Argument | Description |
---|---|
*fieldlist | A comma separated list of the fields for which NullAsValue should be turned on. Using * as field list indicates all fields. The wildcard characters * and ? are allowed in field names. Quoting of field names may be necessary when wildcards are used. |
Example:
NullAsValue A,B;
Set NullValue = 'NULL';
LOAD A,B from x.csv;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Paul for updates, I have 100 such columns do I have to do this for all columns or there is any other workaround?
NullAsValue A,B;
Set NullValue = 'NULL';
LOAD A,B from x.csv;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You're answer is in the helpfile I've pasted earlier.
Using * as field list indicates all fields
Remember to create a default using SET NullValue = '<unknown>';
Also see : http://qlikshare.com/qlikview-video-tutorial-convert-null-values-selectable-strings-qlikview/

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You won't get a more extensive response than Paul's. Please do read it thorougly. It explains how to fix your problem in the last frame (Arguments).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For me, Data, Validity, Criteria, Cell Range, Allow Blank Cells, Show Selection List fails to show an empty item in the dropdown list whether or not I have Sort Entries Ascending. I have also tried putting the empty cell at the beginning, middle, and end of the validity source range and have tried replacing the empty value in the source range with the formula ="" but in every case the dropdown list only contains the nonempty values. The best solution I have to offer is to replace the empty cell in the validity source range with a single blank. I realize that this will populate the target cell with a blank, but perhaps for your situation that's close enough.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Ashish,
You cannot select null as it doesnt exist. but you can create a flag. Look similar discussion here How to select BLANKS or <NULL> values
To handle null in general, follow the link below.
Regards,
Siva

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Paul
You solutions works great for me, thanks for that.
I have 50 such columns and hence , I am using NullAsValue *. However for primary key records there is no null value and its selecting null for primary key as well.
How to avoid some fields if we using NullAsValue*?
Thanks in advance.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This can be used like the QUALIFY; UNQUALIFY; statements. First you do something with all fields, because that's easy. Then and only for the exceptions, you reverse what you have done. Like
:
NULLASVALUE *; // Assign a value to NULL for all fields
NULLASNULL KeyField1, KeyField2, ...; // Set Key Fields back to real NULL handling
:
And this situation lasts as long as you do not specify new NULLASVALUE/NULLASNULL statements.
Best,
Peter

- « Previous Replies
-
- 1
- 2
- Next Replies »