Filter nulls/non nulls on List Box

[amigo007]

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.

[amigo007]

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]

Hi

See Attachment

[tresesco]

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

[amigo007]

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]

Yes.

Change expression in List Box to

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

[vishsaggi]

Try like:

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

[trdandamudi]

Got it. Then use the below expression:

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

[amigo007 · Accepted solution]

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]

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