topic Re: Set Analysis include Null Values only in QlikView

Set Analysis include Null Values only

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

Hi,

I have the attached Document, I want to Count the number of Id's where Status = Active and CompletedDate = NULL

Thanks

Re: Set Analysis include Null Values only

eliko_il — Sun, 12 May 2019 17:53:44 GMT

Hi,

Instead of - count( {$<[Status]={'Active'} >} [ID] )

change the expression to - sum( {$<[Status]={'Active'} >} if(IsNull(CompletedDate),1,0) ).

Re: Set Analysis include Null Values only

ivandrago — Sun, 12 May 2019 18:05:15 GMT

Hi,

Thanks for this, yes, this seems to be returning the correct result but is there another way without using the IF statement as when there is a Large Dataset the calculation will be slower; I did see the functions P() and E() but unsure if this will work,

Re: Set Analysis include Null Values only

Anil_Babu_Samineni — Sun, 12 May 2019 18:08:46 GMT

Either one of the below

=count( {$<[Status]={'Active'}, CompletedDate={"=Len(CompletedDate)=0"} >} [ID] )

=count( {$<[Status]={'Active'}, CompletedDate={"=IsNull(CompletedDate)"} >} [ID] )

=count( {$<[Status]={'Active'}, CompletedDate={"=Null()"} >} [ID] )

=count( {$<[Status]={'Active'}, CompletedDate-={"=Len(CompletedDate)>0"} >} [ID] )

Re: Set Analysis include Null Values only

Vegar — Sun, 12 May 2019 18:15:36 GMT

Maybe the following expression solves your problem.

=count( {$<
[Status]={'Active'} ,
ID = E({<CompletedDate={"*"}>})
>} [ID] )

Re: Set Analysis include Null Values only

ivandrago — Sun, 12 May 2019 18:19:10 GMT

Hi,

None of these Expressions work? Thy all come back with 0.

Re: Set Analysis include Null Values only

Vegar — Sun, 12 May 2019 18:22:40 GMT

Another solution would be to load null as value during script execution.

In the load script
NullAsValue CompletedDate;
Set NullValue = 'NULL';

LOAD
ID,
CompletedDate,
Status,
...
FROM source;

In the expression:
=count( {$<
[Status]={'Active'} ,
CompletedDate={'NULL'}
>} [ID] )

Re: Set Analysis include Null Values only

nicanova — Fri, 17 Mar 2023 15:09:55 GMT

Sorry this is years later, but can you explain why this part of the formula works? ID = E({<CompletedDate={"*"}

I would think that using * excludes where the completed date has any value (or isn't null).

I am trying to count how many interviews result in offers extended to candidates, and my formula works (thanks to you!), but I don't understand why it does:

Count({$<[DateType]={'Interview'},[Offer Extended]=E({"[Offer Extended]={"*"}"})>}[Requisition ID])

Re: Set Analysis include Null Values only

BrunPierre — Wed, 22 Mar 2023 08:25:18 GMT

Hi @nicanova,

An asterisk (*) will return characters (including spaces) except null.

This expression with E() would exclude the Offer Extended field values. It could therefore be interpreted as candidates who did not receive offers.