topic Filter on null values in QlikView

Filter on null values

Mon, 02 Jan 2012 13:06:37 GMT

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.

Malte

Filter on null values

SunilChauhan — Mon, 02 Jan 2012 13:42:13 GMT

use isnull() function

hope this helps

Filter on null values

Mon, 02 Jan 2012 13:51:08 GMT

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

Filter on null values

SunilChauhan — Mon, 02 Jan 2012 13:54:18 GMT

in script

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

hope this helps

Filter on null values

Mon, 02 Jan 2012 14:49:40 GMT

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

Filter on null values

Tue, 03 Jan 2012 14:52:17 GMT

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.

Filter on null values

Tue, 03 Jan 2012 15:19:28 GMT

sorry company 3 and 4

Re: Filter on null values

Miguel_Angel_Baeyens — Tue, 03 Jan 2012 15:27:45 GMT

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

Filter on null values

Tue, 03 Jan 2012 16:21:05 GMT

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

Re: Filter on null values

Miguel_Angel_Baeyens — Tue, 03 Jan 2012 16:34:56 GMT

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