topic Select blank in drop down list qlikview in QlikView

Select blank in drop down list qlikview

ashishpalkar — Tue, 29 Dec 2015 22:02:19 GMT

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,

Re: Select blank in drop down list qlikview

Peter_Cammaert — Tue, 29 Dec 2015 22:13:55 GMT

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

Re: Select blank in drop down list qlikview

p_verkooijen — Tue, 29 Dec 2015 22:17:27 GMT

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/ScriptRegularStatements/NullAsValue.htm)

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;

Re: Select blank in drop down list qlikview

ashishpalkar — Tue, 29 Dec 2015 22:20:03 GMT

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;

Re: Select blank in drop down list qlikview

p_verkooijen — Tue, 29 Dec 2015 22:22:43 GMT

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

Re: Select blank in drop down list qlikview

Peter_Cammaert — Tue, 29 Dec 2015 22:24:34 GMT

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

Re: Select blank in drop down list qlikview

Wed, 30 Dec 2015 05:21:15 GMT

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.

Re: Select blank in drop down list qlikview

Siva_Sankar — Wed, 30 Dec 2015 05:28:39 GMT

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.

NULL handling in QlikView

Regards,

Siva

Re: Select blank in drop down list qlikview

ashishpalkar — Tue, 05 Jan 2016 21:19:33 GMT

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.

Re: Select blank in drop down list qlikview

Peter_Cammaert — Tue, 05 Jan 2016 22:56:41 GMT

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

Re: Select blank in drop down list qlikview

ashishpalkar — Thu, 07 Jan 2016 17:48:10 GMT

Thanks Peter,

Is there any way we can set Null as '-' globally instead of setting on each script tab?

below we are setting Null at only one script, what if there are multiple tabs?

NullAsValue A,B;

Set NullValue = 'NULL';

LOAD A,B from x.csv;

Re: Select blank in drop down list qlikview

Peter_Cammaert — Fri, 08 Jan 2016 08:57:17 GMT

Why do you set it on each script tab?

Script tabs aren't separate scripts (unless you package the code on each tab in separate SUBs) and they are executed sequentially, starting from the leftmost tab.

If you set the NULLASVALUE variable on the first tab (before the first LOAD on that tab), it will be applied for all successive LOADS on all other tabs, until you provide a new SET NULLASVALUE statement or you reset the NULLASNULL state for some fields.

Re: Select blank in drop down list qlikview

Peter_Cammaert — Fri, 08 Jan 2016 09:00:28 GMT

If you don't know what I mean, export your script to a script file using File->Export to Script File... in the script editor. Open the script file (just text) in a text editor (like Notepad) and you'll see how the code on each tab is ordered and tagged.

Peter

PS If you think any other response in this prolonged discussion is useful to you, use Actions->Helpful in the lower left corner to mark the response as such.