topic Re: AND operator in set analysis in QlikView

AND operator in set analysis

Anonymous — Mon, 21 Jul 2014 17:44:04 GMT

Hi all,

I have a pivot table as in the image below, but there's one exception only for AMS (row dim), when the role = 'Guarantee' (the role is not in the pivot), so I should remove those from the "QD" count. But how do I include that in the set analysis to say "where region<>'AMS' AND role<>'Gaurantee' " without loosing the whole AMS row?

=Count({<[Report Date]={"$(MaxDate)"}>} [StatusID])

Re: AND operator in set analysis

datanibbler — Wed, 23 Jul 2014 15:27:35 GMT

Hi Oscar,

you need no AND inside a set_expression. Just separate several set_expressions with a comma.

Re: AND operator in set analysis

Anonymous — Wed, 23 Jul 2014 15:35:38 GMT

Hi DataNibbler,

That doesn't work because it will match/remove any coincidence, if for example I write this

=Count({<[Report Date]={"$(MaxDate)"},REGION-={"AMS"},ROLE-={"Guarantee"}>} [StatusID])

In the pivot table, I'll missing any AMS data and all data with "guarantee" role

Re: AND operator in set analysis

stigchel — Wed, 23 Jul 2014 15:39:58 GMT

You can use the Set Modifier P (possible), which in below example gives a list of possible id's that meet your criteria

=Count({<[Report Date]={"$(MaxDate)"},[Id]=P({1<Region={'<>AMS'},Role={'<>Guarantee'}>} [Id])>} [StatusID])

Re: AND operator in set analysis

gmoraleswit — Wed, 23 Jul 2014 15:42:54 GMT

Do you have the Guarantee role only in the AMS region? Try this:

=Count({<[Report Date]={"$(MaxDate)"},role-={'Guarantee'}>} [StatusID])

If you have the role in other regions and you want to exclude it only for the AMS region then you might need to use an if in your expression:

if(region = AMS, Count({<[Report Date]={"$(MaxDate)"},role-={'Guarantee'}>} [StatusID]), Count({<[Report Date]={"$(MaxDate)"}>} [StatusID]))

or create a new field in your script:

load

if(region=AMS and role='Gaurantee', 0, 1) as flag_validRecord

from...

then in your expression:

Count({<[Report Date]={"$(MaxDate)"},flag_validRecord={1}>} [StatusID])

Re: AND operator in set analysis

jaimeaguilar — Wed, 23 Jul 2014 15:44:06 GMT

Hi,

maybe like this:

=Count({<[Report Date]={"$(MaxDate)"}, Region-={'AMS'}, Role-={'Guarantee'}>} [StatusID])

use -= to exclude values from set analysis

regards

Re: AND operator in set analysis

Anonymous — Wed, 23 Jul 2014 15:54:10 GMT

Try this and confirm

=Count({<[Report Date]={"$(MaxDate)",Region-='AMS',Role-='Gaurantee'}>} [StatusID])

Re: AND operator in set analysis

Anonymous — Wed, 23 Jul 2014 16:00:40 GMT

Happend what I thought, AMS QD value is 0, it should not

Re: AND operator in set analysis

Anonymous — Wed, 23 Jul 2014 21:21:06 GMT

Thanks Piet but it didn't work, I tried E() instead of P() and I get a very similar result of what I'm looking for, but still is not the answer.

Re: AND operator in set analysis

Anonymous — Wed, 23 Jul 2014 21:22:02 GMT

hi Gabriela, I just wanted to find a way to do it with set analysis

Re: AND operator in set analysis

Anonymous — Wed, 23 Jul 2014 21:22:24 GMT

Thx Jaime, but that will remove all AMS and all Guarantee

Re: AND operator in set analysis

stigchel — Thu, 24 Jul 2014 15:02:57 GMT

Hi Oscar, maybe you can use the * intersection operator, something like below.(and maybe E instead of P)

=Count({<[Report Date]={"$(MaxDate)"},[Id]=P({1<Region={'<>AMS'}>})*p({1<Role={'<>Guarantee'}>} [Id])>} [StatusID])

Re: AND operator in set analysis

aveeeeeee7en — Fri, 25 Jul 2014 06:54:19 GMT

See this:

http://community.qlik.com/message/406104

http://community.qlik.com/thread/126883

Regards

Aviral Nag