topic Re: set analysis with isnull not working in QlikView

set analysis with isnull not working

ashmitp869 — Tue, 06 Jul 2021 05:58:06 GMT

Hi there,

Please kindly help me with the set analysis expression :

Sum({<Identifier={'T','L','R'} OR ISNULL(Identifier)>}DISTINCT [Episodes])

The IsNull part is not working , I tried to used OR Len(Identifier) > {0}>} and OR Identifier= {"=Len(Identifier) > 0"}>}
but still red mark is coming.

Re: set analysis with isnull not working

PiEye — Tue, 06 Jul 2021 08:38:17 GMT

Set analysis can only replicate selections that you make in the front end. Anything that you can type in a list box or search bar, can be used in set analysis. You'll see that you can't select nulls.

There are two options here.

The ideal solution is to replace the nulls with a value such as "NA", so that you can actually make a selection with them - see an easy way to do this here .

The value can then be included in set analysis with the others: Sum({<Identifier={'T','L','R','NA'} >}DISTINCT [Episodes])

Other than that you'll have to use some of the set operators to exclude "has a value" and then add back in the ones you want

Sum({-<Identifier={"*" }>+<Identifier={"T" ,"L","R"}>}DISTINCT [Episodes])

Does this work?

Re: set analysis with isnull not working

ashmitp869 — Wed, 07 Jul 2021 00:25:52 GMT

I used a id_null_flag in the script like -if(isNull(Identifier),1,0) as id_null_flag

1. Is this if condition

(IsNull([Identifier]) Or [Identifier]InList("T";"R";"L"))

Similar to qlikview expression

Count({<Identifier={'T','R','L'},id_null_flag ={1}>}DISTINCT [Episodes])

Re: set analysis with isnull not working

PiEye — Wed, 07 Jul 2021 08:04:23 GMT

Hi Ashmit, this expression

Count({<Identifier={'T','R','L'},id_null_flag ={1}>}DISTINCT [Episodes])

Will be similar to an "AND". Similar to what I wrote above, if you imagine you are making the equivalent filters in the front end, this would mean you filter on identifier, then the null flag meaning you end up with the intersection of them both.

In order to get an "OR" you can either:

"Add" two set analysis expressions together, like thus:
- <Identifier={'T','R','L'}>+<id_null_flag ={1}>
Include the null value as a replacement in Identifier (my preffered option) and add it to the list of selected identifiers
- <Identifier={'T','R','L','NA'}> (where NA is the substituted null value)