Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
Highlighted
New Contributor III

Filter nulls/non nulls on List Box

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

IDNameCountryAccount Number
1Name1Country11111
2Name2Country2-
3Name3Country3-
4Name4Country42222
5Name5Country5-

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.

Tags (2)
1 Solution

Accepted Solutions
Highlighted
New Contributor III

Re: Filter nulls/non nulls on List Box

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

View solution in original post

18 Replies
MVP
MVP

Re: Filter nulls/non nulls on List Box

Is this a table box or straight table?

Highlighted
New Contributor III

Re: Filter nulls/non nulls on List Box

Table box.

Highlighted

Re: Filter nulls/non nulls on List Box

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
New Contributor III

Re: Filter nulls/non nulls on List Box

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.

Highlighted
Honored Contributor II

Re: Filter nulls/non nulls on List Box

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

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

Highlighted

Re: Filter nulls/non nulls on List Box

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
MVP
MVP

Re: Filter nulls/non nulls on List Box

Try using straight table like in attached sample.

Capture.PNG

Highlighted
New Contributor III

Re: Filter nulls/non nulls on List Box

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

Highlighted
New Contributor III

Re: Filter nulls/non nulls on List Box

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