Basket analysis

[#1]

Hi all

I've been searching several times on this forum, but still haven't found what i am looking for.

Basically, what i want to do is what items are sold together. E.g - the best match for items is shirt & ties or shoes & socks or even milk & cereals.

I have thought of several ideas how to achieve that, and my best thought so far is creating a table, where all possible combinations of items sold on a transaction will be created. This should give the possiblility to sort on matches with highest frequencies etc. For example:

On transaction 1000111 there is sold 3 items..item 1,2,3. Then i should have 3 outcomes. 1+2,1+3,2+3. Then i could do a count calculation in a chart and get what i need. I just dont know how to do it.

Do any of you have any idea of hove to achive the above. All possible and workable solutions are highly appreciated. The might be a simple (or complex solution i still havent come up with).

Thank you for any help

/Martin

[swuehl]

Michael, I know I am 2 years late, but haven't found too much about basket analysis in the forum or in the demos, so I got your answer as top answer.

You're demo looks interesting, but I have problems to interpret the results. If I open the basket.qvw in QV9 or 10, I get a count of 21 for basket (p + q). In the Excel, I don't see this amount of common basket set.

Do I misunderstand something completely? I would be glad if you could help me, even after 2 years after posting your solution.

Regards,

Stefan

[JonnyPoole]

Hey, this came to me too (another year later !) .  I added another nest to the for loop to accurately list and rank the most common two product combinations.  There were some nulls in this set that may or may not exist in my real life customer situation which i did not account for in this example.

data_src:
LOAD 
     TransID,
     RowNo() as Count,
     Products
FROM data.xls (biff, embedded labels, table is Sheet1$);
let  T   = noofrows('data_src');

for  t   = 1 to T
     tmp:
     LOAD 
          TransID,
          subfield(Products, ',') as ProductsInTransaction
     RESIDENT data_src
     WHERE Count = $(t)  ;
     
     let  P   = noofrows('tmp');
     for p = 0 to P-2
          let ProductInTransaction = peek('ProductsInTransaction', $(p),'tmp');
          let psubstart=p+1;
          let psubend=P-1;

          for psub = psubstart to psubend
                    ProdCombo:
                         LOAD
                              TransID,
                              '$(ProductInTransaction)' & ' & ' & peek('ProductsInTransaction', $(psub),'tmp') as Basket_
                         RESIDENT tmp
                         where recno()=1;
          next

     next
     drop table tmp;

next


Drop table data_src;

 

Also it does only compute the 2 product combinations and not 3 or 4 or more.  (for now).

Message was edited by: Jonathan Poole