topic Re: Filter nulls/non nulls on List Box in QlikView

Filter nulls/non nulls on List Box

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

I have a table on my dashboard in a similar format as follow:

ID	Name	Country	Account Number
1	Name1	Country1	1111
2	Name2	Country2	-
3	Name3	Country3	-
4	Name4	Country4	2222
5	Name5	Country5	-

I need to add a filter/flag on my dashboard in a list box for 'Account Number' field. The header for the list box is something like 'Account Number null?', then the list box would have two selections: 'Yes' and 'No'. So selecting 'Yes' will show only rows with IDs 2, 3 and 5 while 'No' will return the table with rows 1 and 4.

Thanks in advance.

Re: Filter nulls/non nulls on List Box

tresB — Fri, 05 May 2017 13:11:45 GMT

Is this a table box or straight table?

Re: Filter nulls/non nulls on List Box

amigo007 — Fri, 05 May 2017 13:14:25 GMT

Table box.

Re: Filter nulls/non nulls on List Box

Anil_Babu_Samineni — Fri, 05 May 2017 13:15:03 GMT

May be this in script?

LOAD ID,

Name,

Country,

[Account Number],

If([Account Number]='-', 'Yes', 'No') as NullFlag

FROM

[https://community.qlik.com/message/1262813]

(html, codepage is 1252, embedded labels, table is @1);

Re: Filter nulls/non nulls on List Box

amigo007 — Fri, 05 May 2017 13:18:33 GMT

Thanks.

Is there a way to add the flag directly on the dashboard rather from the loading script?

I'm loading my data from SQL server.

Re: Filter nulls/non nulls on List Box

trdandamudi — Fri, 05 May 2017 13:21:59 GMT

Create a list box and in the expression use the below code :

=If(Left([Account Number],1)='-','Yes','No')

Re: Filter nulls/non nulls on List Box

Anil_Babu_Samineni — Fri, 05 May 2017 13:22:31 GMT

NP, Just add one line script after generating the code.

Connection string of SQL Server

Load *,

If([Account Number]='-', 'Yes', 'No') as NullFlag

From <Data Source od SQL Server>;

Re: Filter nulls/non nulls on List Box

tresB — Fri, 05 May 2017 13:22:47 GMT

Try using straight table like in attached sample.

Re: Filter nulls/non nulls on List Box

amigo007 — Fri, 05 May 2017 13:34:13 GMT

Thanks.

I'm having a big table box with a lot of data, so a solution that could work with a table box will be highly appreciated.

Also, for the list box, is there a possibility to have 'Yes' and 'No' as a check boxes? i.e. It will be allowed also to display the table with all the data (if 'Yes' and 'No' are not checked).

Re: Filter nulls/non nulls on List Box

amigo007 — Fri, 05 May 2017 13:36:21 GMT

Actually the values are null and not '-'. I guess QlikView displays nulls as '-' in tables.

Will a similar syntax as SQL work here as well? i.e. If([Account Number]) is NULL...

Re: Filter nulls/non nulls on List Box

amigo007 — Fri, 05 May 2017 13:40:30 GMT

Thanks.

This looks as a simple and a nice solution, the only thing I have null values and not '-'. I guess QlikView displays nulls as '-' in tables. So '-' character can not be used in the condition.

Re: Filter nulls/non nulls on List Box

antoniotiman — Fri, 05 May 2017 13:41:44 GMT

See Attachment

Re: Filter nulls/non nulls on List Box

tresB — Fri, 05 May 2017 13:42:33 GMT

With table box, it might not be possible that way. PFA for 'All' option solution.

Re: Filter nulls/non nulls on List Box

amigo007 — Fri, 05 May 2017 13:47:22 GMT

Thanks.

This looks great but the issue is the same as I mentioned to Thirumala below, I'm having nulls in my data and not '-' which is just displayed By QlikView when values are null.

Re: Filter nulls/non nulls on List Box

antoniotiman — Fri, 05 May 2017 13:59:20 GMT

Yes.

Change expression in List Box to

=If([Account Number]='-' or Len([Account Number])=0,'Yes','No')

Re: Filter nulls/non nulls on List Box

vishsaggi — Fri, 05 May 2017 14:15:21 GMT

Try like:

= IF(Len(Trim([Account Number])) = 0 , 'Yes', 'No')

Re: Filter nulls/non nulls on List Box

trdandamudi — Fri, 05 May 2017 14:36:49 GMT

Got it. Then use the below expression:

=If(Len(Trim([Account Number]))>0,'No','Yes')

Re: Filter nulls/non nulls on List Box

amigo007 — Mon, 08 May 2017 20:25:48 GMT

Thanks all for your help. All the suggestions make sense but still didn't get my problem solved 100%. After a few researches, it turned out that it's tricky how QlikView handles the null values. I read a great document from this thread and concluded that converting null values to a string, e.g. <NULL>, in the load script and then applying your suggested script did the trick.

=If(([Account Number])<>'<NULL>','No','Yes')

PS: data source from SQL Server

Re: Filter nulls/non nulls on List Box

vishsaggi — Mon, 08 May 2017 20:33:26 GMT

Glad you found out. Close the Thread marking yours as Correct and mark if any helpful.