Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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.

18 Replies
amigo007
Contributor III
Contributor III
Author

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.

antoniotiman
Master III
Master III

Hi

See Attachment

tresesco
MVP
MVP

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

amigo007
Contributor III
Contributor III
Author

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.

antoniotiman
Master III
Master III

Yes.

Change expression in List Box to

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

vishsaggi
Champion III
Champion III

Try like:

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

trdandamudi
Master II
Master II

Got it. Then use the below expression:

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

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

vishsaggi
Champion III
Champion III

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