Count of orders with specific details

[#1]

Hi everyone,

I'm trying to count the orders that in the details contains:

- at least a product in the list A,B,C

- but can contains 0 or more H,J,K.

- and must NOT contains E,R,T

The next step is to create a dimension with these different aggregations.

For example.

"A B C" Orders: 200

"E R T" Orders: 500 (must contains one of E,R,T, can contains H,J,K. must NOT contains A,B,C).

Does anyone knows how can I solve this?

Thanks

Marco

[johnw]

Perhaps this?

LEFT JOIN ([Orders])
LOAD
Order
,if("Contains ABC" and not "Contains ERT","Order") as "ABC Order"
,if("Contains ERT" and not "Contains ABC","Order") as "ERT Order"
;
LOAD
Order
,max(match(Product,'A','B','C'))>0 as "Contains ABC"
,max(match(Product,'H','J','K'))>0 as "Contains HJK" // Not actually needed
,max(match(Product,'E','R','T'))>0 as "Contains ERT"
RESIDENT [Order Details]
GROUP BY Order
;

count("ABC Order")
count("ERT Order")