Filter on null values

[#1]

Hi.

I have a problem that I'm sure you can help me out with.

In a table, how do I do to filter on the fields with null values? When I click on them now nothing happands.

BR

Malte

[SunilChauhan]

use isnull() function

hope this helps

[#3]

Sorry I'm a rookie. Where do I use this expression?

[SunilChauhan]

in script

if(isnull(fieldname),1,0)  as Null

hope this helps

[#5]

The script works with this expression but I can still not filter on the NULL values

[#6]

Maybe if I explain the problem in more detail

Column A | Column B    | 

Company1 |      15           | 

Company2 |      20           |               

Company3 |                    |                

Company4 |                    |               

Company5 |      15           |                

In this table i want to click in the cells that miss values to see Company4 and 5.

[#7]

sorry company 3 and 4

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

Null values are null in QlikView, meaning that you cannot select them because they are not attached to any specific value for the dimension. Use the flag suggested above in your script, but using Len() instead of IsNull() (nothing wrong with the latter, but it returns unexpectedly in some cases). Assuming this is your script, create a new flag field with "1" for all fields with no ColumnB and 0 otherwise

Data:
LOAD *, If(Len("Column B"), 0, 1) AS NullFlag INLINE [
Column A, Column B 
Company1, 15
Company2, 20
Company3
Company4
Company5, 15
];

So now you can sum the NullFlag value to count how many records have a value.

You can also use the MissingCount() and NullCount() in your charts, just in case.

Hope that helps.

Miguel

[#9]

The script runs without errors but the NULL-fields are still emty and I can't filter on them.

[Miguel_Angel_Baeyens]

Hi,

Sorry if I didn't make myself clear in my previous post. You cannot select null values in charts, so if you want to somehow be able to select those values, you can create a flag field so if the field "Column B" is empty, blank or has a null (i.e.: when the length of the field is zero) it will add a "1" to the NullFlag field, otherwise, it will add a "0".

So now you have a field named NullFlag with two possible values, 1 and 0. If you create a listbox and click on the 1, you will see only those records that have a null value in "Column B".

If you mean an expression in a chart instead of a value, then you simply cannot select null values, and you will have to draw a new chart using this flag field to represent all those values in the dimension that don't have anything for the aggregation.

Hope that makes sense.

Miguel