Select blank in drop down list qlikview

[ashishpalkar]

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,

[Peter_Cammaert]

'-' 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

[p_verkooijen · Accepted solution]

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;

[ashishpalkar]

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;

[p_verkooijen]

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/‌

[Peter_Cammaert]

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).

[#7]

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.

[Siva_Sankar]

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 &lt;NULL&gt; values

To handle null in general, follow the link below.

NULL handling in QlikView

Regards,

Siva

[ashishpalkar]

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.

[Peter_Cammaert]

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