topic Multiple filters in set analysis? in QlikView

Multiple filters in set analysis?

Tue, 20 Mar 2012 17:25:05 GMT

Hi all,

I'm pretty new here so go easy on me!

Trying to run set analysis that runs a count distinct function, but that filters on two fields. While this should be easy, one of the filters needs to select null values, and another needs to filter on a value in a different field.

I have a working solution to select the null values:

count({$-<[new] = -{}>} distinct [key])

But what I would like to have is something like:

count({-<[new] = -{}>}, [state] = {"APPS"}>} distinct [key])

But I know this isn't correct due to this specific syntax required for nulls..

So a couple of questions: how would I create and expression that used 'not in' syntax in the first filter (select <> "No" instead of select Null. Or, how can I embed these two types of filters into one expression?

Hope this makes sense. Thanks in advance!

Multiple filters in set analysis?

Wed, 16 May 2012 20:49:11 GMT

Try this

count({-<[new] -= -{'No'}>}, [state] = {'APPS'}>} distinct [key])

Multiple filters in set analysis?

vgutkovsky — Thu, 17 May 2012 17:05:19 GMT

Something like this should work:

count({<state={'APPS'},key={"=len(trim(new))=0"}>} distinct key)

This will select rows where "state"=APPS and "new" is null.

Regards,

Vlad

Multiple filters in set analysis?

Thu, 17 May 2012 17:50:06 GMT

Another thing you could do is to transform this Null values on your script, and stating an especific value.

For example suppose your field (in which you want to select nulls) is called fNull

Load

if(fNull = Null, 'nullField',fNull) as fNull

...etc...

This should make all null values equal to 'nullField', try diferent values depending on the field format(for example if it is a number try putting -1 or else)

after that in the set analysis you won't need to look for nulls but for values equal to 'nullField' filling that spot where should be a null value.

Hope this helps.

best