topic Re: Regarding intersection condition in qlikview script part in QlikView

Regarding intersection condition in qlikview script part

ak00428233 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Regarding intersection condition in qlikview script part

sunny_talwar — Sun, 04 Mar 2018 12:42:40 GMT

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....

Re: Regarding intersection condition in qlikview script part

ak00428233 — Sun, 04 Mar 2018 14:23:09 GMT

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.

Re: Regarding intersection condition in qlikview script part

vishsaggi — Sun, 04 Mar 2018 16:14:02 GMT

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;

Re: Regarding intersection condition in qlikview script part

ak00428233 — Sun, 04 Mar 2018 16:49:10 GMT

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?

Re: Regarding intersection condition in qlikview script part

vishsaggi — Sun, 04 Mar 2018 16:59:18 GMT

Yes you can.