Filter using "and" and "Or" on the same field

[mostafahashkil]

I have a field that contains codes for products. some of these products can have more than one code at the same time.

I want to give the user the ability to filter products by Product Code as follows:

get all product which has the following codes:  CodeA and CodeB and (CodeC or CodeD)

Please notice that the product can have either codeC or codeD but not both at the same time, that's why I can not use "and" in addition to the other two code (CodeA and CodeB)

Please advise me on how can I apply this in Qlikview?

[edwin]

did you want it in a chart? 

create straight table chart with Product as dimension add this expression:
=if(count({<Code={'CodeC','CodeD'}> *<Product=p({<Code={'CodeA'}>}Product)>*<Product=p({<Code={'CodeB'}>}Product)>}Product)=1,1)

if the count of records for a Product with either C or D = 1 (meaning must have only one of either) and has A and has B, return 1

only products that satisfy the condition will be listed.  if you want this as a list instead, hide the expression in Presentation and it appears like a list.

[mostafahashkil]

Thanks for the reply,

It's not just CodeA, CodeB, CodeC

Here's the story, I have a table that contains the following fields: Product and Product Code, Production Date. I'm creating production statistics for the products according to the codes which will count per month, quarter, Year ( I don't want to distract you with more unneeded details). so I've created a pivot table that will list the statistics and I have the codes in a list box. the list box either supports "and" or "or" but not both. I need to let the user filter the way I described above or the statistics will not be correct.

Users should specify the products they want to get the statistics for by specifying the codes.

Note: there are many codes, It's not just CodeA, CodeB ... etc these were just an example. but the common pattern as I described above. CodeA and CodeB and ( CodeC or CodeD).

another example: CodeF and ( CodH or CodeS) and CodeA.

So, the question is how can I give the user the ability to specify this on the interface?

[edwin]

the principle of filtering in QlikView is select Values of Fields (dimensions) and all of them are ANDed.  adding the business rule can only have one of C or D cant be described in a list where the user selects a value.  to implement the AND OR, you need some sort of expression incorporated in some object list a chart. 

if you have two lists: 1 lists allows the user to select CODES that are ANDed and a list of codes that are mutually exclusive (your ORs) and then an object that shows which products satisfy the conditions being

must have all the codes in the AND list and must have only 1 in the OR list - would that help?

if it does, i would list all the CODES - let the user select the codes to be ANDed - that actions automatically addresses the AND part of the requirement.  then create an Island table of all possible CODEs - let the user select that.  then create an expression that counts the rows where the code is in the list of the codes from the OR list 

=if(count({<Code={$(vOrList)}>}Product)=1,1)

where vOrlist = chr(39) & concat(CodeOrList, chr(39) & ',' & chr(39)) & chr(39).

so if the user selects codes A and B (Product is automatically filtered for these codes and select C and D in the code field CodeOrList (from teh codes in the island table) then the expression is evaluated as 

=if(count({<Code={'C','D'}>}Product)=1,1)

[edwin]

should have tested the theory first.

see attached.  first table shows products with A or B, 2nd chart shows those with C and D.  last chart shows those with both A and B, and with only 1 of C and D

[edwin]

the principle is the total count should =

count of products selected in the AND list + 1 (1 is count of product in the OR list)