Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Bobi
Partner - Contributor II
Partner - Contributor II

Intersection of dimensional values

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!



 

Labels (1)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

ice-cream and chocolate:

=Count(
{<[Order-id] = P({<productlevel_1= {'ice-cream'}>})>
* <[Order-id] = P({<productlevel_1= {'chocolate'}>})>}

DISTINCT [Order-id]) 

ice-cream and not chocolate:

=Count(
{<[Order-id] = P({<productlevel_1= {'ice-cream'}>})>
* <[Order-id] = E({<productlevel_1= {'chocolate'}>})>}

DISTINCT [Order-id]

 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

View solution in original post

2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

ice-cream and chocolate:

=Count(
{<[Order-id] = P({<productlevel_1= {'ice-cream'}>})>
* <[Order-id] = P({<productlevel_1= {'chocolate'}>})>}

DISTINCT [Order-id]) 

ice-cream and not chocolate:

=Count(
{<[Order-id] = P({<productlevel_1= {'ice-cream'}>})>
* <[Order-id] = E({<productlevel_1= {'chocolate'}>})>}

DISTINCT [Order-id]

 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Bobi
Partner - Contributor II
Partner - Contributor II
Author

Thank you very much!