Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ivandrago
Contributor II

Set Analysis include Null Values only

Hi,

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

Thanks

1 Solution

Accepted Solutions
Partner
Partner

Re: Set Analysis include Null Values only

Maybe the following expression solves your problem.

=count( {$<
[Status]={'Active'} ,
ID = E({<CompletedDate={"*"}>})
>} [ID] )
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
6 Replies
eliko_il
New Contributor

Re: Set Analysis include Null Values only

Hi,

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

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

ivandrago
Contributor II

Re: Set Analysis include Null Values only

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

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] )

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: Set Analysis include Null Values only

Maybe the following expression solves your problem.

=count( {$<
[Status]={'Active'} ,
ID = E({<CompletedDate={"*"}>})
>} [ID] )
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
ivandrago
Contributor II

Re: Set Analysis include Null Values only

Hi,

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

Partner
Partner

Re: Set Analysis include Null Values only

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] )
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.