topic Re: Counting null values using Set Analysis in QlikView

Counting null values using Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I have a query regarding counting null values using Set Analysis.

Change Order Number	Check
CR1234	-
CR4567	-
CR7891	-
CR2345	1
CR3456	0
CR5643	-
CR1234	-
CR1234	-

The data being used is as mentioned above. I need to do a distinct count of all the change orders having a check value of '-' ie. null or '1'. I have to exclude all the change orders having a check value '0'.

Suggestions are much appreciated.

Thanks and regards,

Erwin Palani

Re: Counting null values using Set Analysis

Kushal_Chawda — Fri, 26 May 2017 07:09:43 GMT

try this creating flag in script

LOAD *,

if(len(trim(Check))=0 or isnull(Check),1,0) as CheckFlag

FROM table;

Now in set analysis

=Count(DISTINCT {<CheckFlag={1}>}[Change Order])

Re: Counting null values using Set Analysis

antoniotiman — Fri, 26 May 2017 07:13:56 GMT

Hi,

may be

=Count({<[Number Check]-={0}>} DISTINCT [Change Order]) -> 5

Regards,

Antonio

Re: Counting null values using Set Analysis

anjali0108 — Fri, 26 May 2017 07:15:12 GMT

Yes ,the above script will work for you,

Also,

If you will get values like shown above only,

then you can go with,

=Count(DISTINCT {<Check-={0}>}[Change Order]) as well.

Re: Counting null values using Set Analysis

Anonymous — Fri, 26 May 2017 07:31:23 GMT

Hi Anjali,

Thanks for your response. But this counts only the Change Order's with check value '1', it doesn't count the null values.

Re: Counting null values using Set Analysis

Marcellino_Groothof — Fri, 26 May 2017 07:32:33 GMT

Hi,

Try this:

Greetings,

Marcellino

Re: Counting null values using Set Analysis

tamilarasu — Fri, 26 May 2017 07:57:54 GMT

Hi Joshua,

Try,

=Count({<Check={"=Len(Trim(Check))=0"} > + <Check={"1"}>}[Change Order Number])

Re: Counting null values using Set Analysis

sunny_talwar — Fri, 26 May 2017 09:34:58 GMT

I don't think that the first part of the set analysis will work... but with a slight change it could work

=Count(DISTINCT {<[Change Order Number]={"=Len(Trim(Check))=0 or Check = 1"}>}[Change Order Number])

I added DISTINCT and Check = 1, but these may still work your way (without DISTINCT and <Check = {1}>), but thought of changing them to streamline the expression.