How to implement SQL NOT IN in set analysis

[agni_gold]

How to implement SQL NOT IN in set analysis

[Anonymous]

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]

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

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

[vikasmahajan]

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

Vikas

[agni_gold]

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]

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]

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