Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
amigo007
Contributor III
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.

1 Solution

Accepted Solutions
amigo007
Contributor III
Contributor III
Author

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
tresesco
MVP
MVP

Is this a table box or straight table?

amigo007
Contributor III
Contributor III
Author

Table box.

Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
amigo007
Contributor III
Contributor III
Author

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.

trdandamudi
Master II
Master II

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

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

Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
tresesco
MVP
MVP

Try using straight table like in attached sample.

Capture.PNG

amigo007
Contributor III
Contributor III
Author

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

amigo007
Contributor III
Contributor III
Author

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