topic Re: How to implement SQL NOT IN in set analysis in QlikView

How to implement SQL NOT IN in set analysis

agni_gold — Sun, 30 Nov 2014 06:46:42 GMT

How to implement SQL NOT IN in set analysis

Anonymous — Sun, 30 Nov 2014 10:16:48 GMT

Hi Agnivesh,

In UI : The E() operator (E for Exclude) would be what you need to use in set analysis to exclude subsets of Data.

=Count({<[Customer Name]=e({<[Call Opened - Date/Time]={'>=$(vBeginTime)<=$(vEndTime)'}>}[Customer Name])>}[Customer ID])

Scenarios to use ‘Not in’ keyword in script:

load

....

from

.....

where not(match("Customer",'Jane','Ann','Fiona'));

(OR)

load

....

from

.....

where Customer not in('Jane','Ann','Fiona');

Qlikview recognizes Match keyword whereas SQL doesn’t.

So use above scenarios as required.

Regards

Neetha

anbu1984 — Sun, 30 Nov 2014 12:07:30 GMT

Use -= to implement 'Not in' in Set analysis.

Sum({<Country-={'INDIA','US'}>} Sales)

vikasmahajan — Sun, 30 Nov 2014 12:30:14 GMT

-= {'USA','INDIA','JAPAN'} you can use in set analysis

Vikas

agni_gold — Sun, 30 Nov 2014 13:04:50 GMT

Thanks

i am trying to convert my sql query in set analysis

select sum(Cost) from Cost_Table where ID in (Select ID from Person_Table where EMP_ID in (select EMP_ID from Employee_Table where Zone_ID=12))

agni_gold — Sun, 30 Nov 2014 13:22:19 GMT

Thanks

i am trying to convert my sql query in set analysis

select sum(Cost) from Cost_Table where ID in (Select ID from Person_Table where EMP_ID in (select EMP_ID from Employee_Table where Zone_ID=12))

anbu1984 — Mon, 01 Dec 2014 05:56:35 GMT

=Sum({<Zone_ID={12}>}Cost)