topic Re: Using an Expression in a List Box/Text Box for selecting Nulls in QlikView

Using an Expression in a List Box/Text Box for selecting Nulls

ZoeM — Wed, 25 Nov 2020 16:16:04 GMT

Hello Community.

I have a bit of a dilemma in my Dashboard.

I need to create a way to select nulls in my dahsboard so that the user can view information pending (not yet completed).

Ordinarily, I would like to use a Text Box with an action to Select In Field all the nulls, and I am using the below formula:

=If(IsNull([purchase date]),' ',0)

I created a List Box to see whether I would be able to select the nulls based on the two criteria: Declared and Not declared and the List Box does show the two options but I am unable to select either.

=(If(Len([purchase date])<1,'Missing','Purchased'))

Any ideas on how I can select all the nulls within a dimension? Ideally, in the Straight Table I created an expression to identify all the nulls within one of the dimensions either as 1 for not null and 0 for nulls.

Thanks for your time and hope something gets to work!

Re: Using an Expression in a List Box/Text Box for selecting Nulls

ogautier62 — Tue, 10 Jul 2018 14:55:31 GMT

Hi,

in your listbox [purchase date] : select all values

then in an other field, sales for example : select excluded values

==> so you'll have null values of date

regards

Re: Using an Expression in a List Box/Text Box for selecting Nulls

sunny_talwar — Tue, 10 Jul 2018 15:30:42 GMT

Why don't you create a new field while loading the data

If(Len([purchase date])<1,'Missing','Purchased') as MissingFlag

and not select Missing and it should work for you...

Re: Using an Expression in a List Box/Text Box for selecting Nulls

ZoeM — Tue, 10 Jul 2018 15:40:27 GMT

Thanks Sunny for your answer, that will work. Is there any way of doing it without having to create a new field when loading the data? Because the data fields will change going forward as the data set increases which means that I or another administrator will have to remember to add a new line item...

Re: Using an Expression in a List Box/Text Box for selecting Nulls

sunny_talwar — Tue, 10 Jul 2018 15:47:55 GMT

May be load with NULLASVALUE to convert nulls into empty spaces

NULLASVALUE *;

SET NullValue = '';

LOAD Toy,

Cost,

[# Items],

[Purchase Date]

FROM

[Null Selection test.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Using an Expression in a List Box/Text Box for selecting Nulls

ZoeM — Tue, 10 Jul 2018 15:59:42 GMT

And that is exactly what I have been looking for!

thanks so much Sunny!