Hi. What is the best solution for getting the intersection of two dim-field values in a table?
Lets say we have a order-id (connected to a customer) and one order-id can have several orderline-id (same customer buying several products results in order-id with several orderlines).
Order-id orderline-id productlevel_1 product_level_2
1 ab ice-cream ice cream with banana
1 abc chocolate Chocolate-cake
2 abcd ice cream ice-cream with apple
3 efg ice-cream ice cream with apple
3 fgh chocolate chocolate-candy
In this case I want to count the intersect of everybody having bought both ice-cream and chocolate (prod_level_1) (but not only chocolate or only ice cream). It should give 2 in this case (order id 1 and 3).
I have tried a few ways:
1. Gives result but probably not correct results?
sum(aggr(count({< product_level_1= {'ice cream'}>} product_level_1) * count({< product_level_1 = {'Chocolate'}>}product_level_1), order_id))
2. Gives 0.
count({<product_level_1 *= {'Chocolate,'ice cream'}>}distinct order_id)
3. Gives 0.
count({<product_level_1 = {'Chocolate'}>*<product_level_1 ={'ice cream'}>}distinct order_id)
If you have suggestions on how to solve it in SQL or in the script in a better way thats fine too. I also want to calculate the ones who only buy ice cream but not chocolate.
Thanks in advance!
