topic Re: How to use Isnull() function in Set analysis in QlikView

How to use Isnull() function in Set analysis

vipingarg23 — Wed, 17 Jan 2018 10:20:09 GMT

Hi Team,

How can we implement Isnull() function in Set analysis?

Re: How to use Isnull() function in Set analysis

prma7799 — Wed, 17 Jan 2018 10:28:31 GMT

Like this??

sum({($ -<Code={'*'}>) >} Amount)

Re: How to use Isnull() function in Set analysis

zhadrakas — Wed, 17 Jan 2018 10:29:01 GMT

what do you want to do ?

i think you can somethink like

count({$<qty={"*"}>}checknum)

-> this will Count everything that is not null

count({$<qty-={"*"}>}checknum)

-> this will Count everything that is null

Re: How to use Isnull() function in Set analysis

prma7799 — Wed, 17 Jan 2018 10:29:35 GMT

also check this link

How to handle NULL values, ANDs and ORs in SET ANALYSIS: | QlikView Help, Tips & Hints

Re: How to use Isnull() function in Set analysis

manoranjan_d — Wed, 17 Jan 2018 10:29:45 GMT

Sum({<field1-={"*"}>} field2)

Re: How to use Isnull() function in Set analysis

VishalWaghole — Wed, 17 Jan 2018 10:31:10 GMT

=sum({<DimensionField-={*}>} FactField)

Re: How to use Isnull() function in Set analysis

vipingarg23 — Wed, 17 Jan 2018 11:06:30 GMT

Sorry guys. But Nothing is working out. I am explaining more with example:

I want count of A where B is null. So I am using below formula:

Count({<B-={"*"}>}A)

But it is not working.

Re: How to use Isnull() function in Set analysis

Anil_Babu_Samineni — Wed, 17 Jan 2018 11:08:05 GMT

If A and B are associate each then, Try this? Or post sample to test

Count({<B = {"=Null()"}>} A)

Re: How to use Isnull() function in Set analysis

teempi — Wed, 17 Jan 2018 11:10:26 GMT

Hi,

I don't think that count({$<qty-={"*"}>}checknum) gives the correct results since nulls cannot be selected. Something like this will have to be used and seemed to work in a simple example application:

count( {$-<qty={"*"}>} checknum)

-Teemu

Re: How to use Isnull() function in Set analysis

teempi — Wed, 17 Jan 2018 11:11:27 GMT

Hi,

Try this: Count({$-<B={"*"}>}A)

-Teemu

Re: How to use Isnull() function in Set analysis

vipingarg23 — Wed, 17 Jan 2018 13:55:17 GMT

Hi Anil,

Please find the sample file.

Here I want count of B where A is null.

Re: How to use Isnull() function in Set analysis

pradosh_thakur — Wed, 17 Jan 2018 13:58:31 GMT

Count({<B={"=(len(trim(B))=0)"}>}A)

Count(DISTINCT {<B={"=(len(trim(B))=0)"}>}A)

Re: How to use Isnull() function in Set analysis

Anil_Babu_Samineni — Wed, 17 Jan 2018 13:59:37 GMT

Looks like, This is working?

Count(If(Len(A)=0,B))

Re: How to use Isnull() function in Set analysis

vipingarg23 — Wed, 17 Jan 2018 14:02:00 GMT

Great Anil, It is working fine..Thanks for your quick reply.

Re: How to use Isnull() function in Set analysis

pradosh_thakur — Wed, 17 Jan 2018 14:02:59 GMT

This didn't work ?

Re: How to use Isnull() function in Set analysis

vipingarg23 — Wed, 17 Jan 2018 14:12:17 GMT

Anil, How can we use it in set analysis?

Re: How to use Isnull() function in Set analysis

Anil_Babu_Samineni — Wed, 17 Jan 2018 14:15:22 GMT

Count({<A = {"=Len(A)=0"}>} B)

Re: How to use Isnull() function in Set analysis

vipingarg23 — Wed, 17 Jan 2018 14:23:20 GMT

Great Anil. Thanks..

Re: How to use Isnull() function in Set analysis

MohitSharma3110 — Tue, 07 Jul 2020 10:22:40 GMT

I don't think this is working, as how can we compare value of A with a condition equaling length of that field =0. if we have below example values of table:

A B
1 100
NULL 200
Then how can we do this set analysis when A= {"=Len(A)=0"}>}.

Re: How to use Isnull() function in Set analysis

john_oll — Mon, 05 Oct 2020 15:07:30 GMT

Count ( { $ - < B ={"*"} > } A)
Is the correct solution.
Take everything and subtract the non-null() values, and your result is only the isnull() values.

Credits to teempi !