topic Re: Re: Count combination of dimension in QlikView

Count combination of dimension

bbi_mba_76 — Mon, 30 Dec 2013 07:03:57 GMT

Hi,

in my application I have orders with products, I need to count the orders with single products or combinations of product.

For example I have OrderID O0001 with product P1 and P2, so I would count this order for the combination of P1 and P2 (P1+2), Order O0002 has only the product P2, so I count this order as P2, O0003 will be counted for P2+3+4...

In the attached file a sample, the expeted result is

combination | orders count

P1	1
P1+2	1
P1+3+4+6	1
P2	2
P2+3+4	1
P3	2
P3+4	1
P3+4+6	1
P3+6	1
P4	1
P4+5+7	1
P5	1
P5+7	1
P6	1
P7	1

Re: Count combination of dimension

jvishnuram — Mon, 30 Dec 2013 07:58:20 GMT

Hi BBI,

I was trying your requirement, it was quiet complex and one thing i want to suggest to you., see the attachment below in that table i have made a pivot in which the orderid which is in bolder is having more than one contribution of the products. can you please check if it is fine for you or not.

Re: Count combination of dimension

bbi_mba_76 — Mon, 30 Dec 2013 08:07:11 GMT

Thanks, in your solution I can see which orders have more products, but I need to count every product combination. The question is: in how many orders I sell P1&P2? and P3&P4? and so on.

Maybe I have to create a cross table in the script, and then flags every order for the correct combination...

Re: Re: Count combination of dimension

jvishnuram — Mon, 30 Dec 2013 08:15:14 GMT

Hi BBI,

Check this attachment.

Re: Count combination of dimension

bbi_mba_76 — Mon, 30 Dec 2013 08:20:39 GMT

Thanks! I removed the dimension OrderID and using a calculated dimension =aggr(Concat(DISTINCT Product,'+'), OrderID) I obtain the correct result.

Re: Count combination of dimension

rbecher — Thu, 17 Jul 2014 18:51:31 GMT

Interesting solution but doesn't give all possible combinations of bought products, let's say for orde O0007:

P1+P3

P1+P4

P1+P6

P3+P4

P3+P6

P4+P6

P1+P3+P4

P1+P3+P6

P1+P4+P6

P3+P4+P6

P1+P3+P4+P6

Any further idea? I guess therefor a complex script loop would be needed..

- Ralf