Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
ak00428233
Contributor III
Contributor III

Regarding intersection condition in qlikview script part

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

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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;

View solution in original post

5 Replies
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
Contributor III
Contributor III
Author

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
Champion III
Champion III

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
Contributor III
Contributor III
Author

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
Champion III
Champion III

Yes you can.