Regarding intersection condition in qlikview script part

[ak00428233]

Hi All,

Could you please suggest me the intersection using where clause in the qlikview script.

Ex:There are four categories,A,B,C,D.I dont want users of  A category and B Category Users to be visible in the report and also few users who might be both in A and C.

Load *

Inline[

]

where

category <>A

and Category <>B.

Could you please suggest me the expression to exclude who are in A and C Category.

Thanks in advance.

Regards,

Anu

[sunny_talwar]

So, you want to exclude users who are in category A and C... you are not necessarily looking to remove everything in Category A and C... what I mean is if you have data like the below table

User     Category

ABC          A

ABC          C

ABC          F

ABC          G

DEF          K

DEF          A

DEF          J

PHL          A

PHL          C

you would want to exclude ABC and PHL because they both have both A and C as category, but DEF will be included because he has only A....

[ak00428233]

Hi Sunny,

Thank you for the quick response.

Yes I want to exclude ABC and PHL as they both have A and C as category.

Kindly suggest the condition to write in script part.

Thanks in advance.

Regards,

Anu.

[vishsaggi · Accepted solution]

I am not sure if this is what you are looking for may be sunny has better way of presenting this script. However, please find below and let me know.

Referring to Sunny's data set:

Cat:

LOAD *  INLINE [

User,     Category

ABC,          A

ABC,          C

ABC,          F

ABC,          G

DEF,          K

DEF,          A

DEF,          J

PHL,          A

PHL,          C

];

Inner Join(Cat)

LOAD User, Sum(IF(Match(Category, 'A', 'C'), 1,0)) AS CatFlag

Resident Cat

Group By User;

NoConcatenate

Final:

Load *

Resident Cat

Where CatFlag = 1;

Drop Table Cat;

[ak00428233]

Thank you Nagaraju.this is my required and it worked well.

I have a small doubt if i want to remove the same data from 3 categories.Can I write the condition as Sum(IF(Match(Category, 'A', 'B','C'), 1,0)) AS catflag?

[vishsaggi]

Yes you can.