topic Re: Set Analysis - Nulls, Multiple Conditions, Ors in QlikView

Set Analysis - Nulls, Multiple Conditions, Ors

Fri, 19 Apr 2013 14:50:25 GMT

Hi everyone,

I'm trying 3 things in this set analysis that I am not too comfortable with yet.

My code currently looks like this:

Count({$-<Employee.Manager = {'*'}, User.ID = {'90*'}, User.ID = {'5*'}>} DISTINCT User.ID)

Count({$<Employee.Manager = {'Not assigned'}, User.ID = {'90*'}, User.ID = {'5*'}>} DISTINCT User.ID)

I want to Count DISTINCT User.IDs where the Employee.Manager is null, or is Not assigned. I also only want to select user IDs that begin with 90 or 5.

Note that I tried replacing all null values of Employee.Manager with 'Not assigned', but I think if User.ID doesn't exist in the Employee table, it'll show up as null no matter what.

Thank you!

Re: Set Analysis - Nulls, Multiple Conditions, Ors

Fri, 19 Apr 2013 22:56:39 GMT

Hi,

Try with this:

Count({$<Employee.Manager = {"=Isnull([Employee.Manager])", 'Not assigned'}, User.ID = {'90*', '5*'} >} DISTINCT User.ID)

Regards,

Ricardo

Re: Set Analysis - Nulls, Multiple Conditions, Ors

Sat, 20 Apr 2013 08:32:40 GMT

Hi,

Just similar to the above solution and can also try this

=Count({$ <Manager={ "=len(Manager)=0" , 'Not Assigned'}, UserID={'5*','9*'}> }DISTINCT UserID)

//Yusuf

Re: Set Analysis - Nulls, Multiple Conditions, Ors

Wed, 01 May 2013 18:17:05 GMT

Thank you for the suggestions.

Ended up getting it with:

=Count({$<UserID= {"=Len(Trim(Manager))=0"}>} DISTINCT UserID)

I'm still confused as to why it's ( {<A = {B}>} A) and not ( {<B = {B}>} A)... but it works!

Re: Set Analysis - Nulls, Multiple Conditions, Ors

Tue, 07 Jan 2014 14:50:08 GMT

I was as confused as you looking at this, but I reasoned out what it's doing. Glad I found this though.

({<A={B}>} A)

the trick is that B isn't s field but a boolean operation confirming whether to include that A or not.

replace B above with

'is B null' returning true or false.

the set analysis is then if A=TRUE .. distinct A

But this isn't saying that A equals TRUE, but more simply, true - A is a valid A