topic Basket analysis in QlikView

Basket analysis

Wed, 29 Apr 2009 23:59:55 GMT

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

Basket analysis

Thu, 30 Apr 2009 01:28:16 GMT

Yes, you're definitely on the right path. I don't use combinations of items when I build mine but I think your plan makes good sense.

What I do is add a table of all combinations of Transaction ID and Item ID, lets call this table COMBO. The Transaction ID field is a linking field, but Item ID does not link to any existing fields. If I need Item Description, I duplicate that table to link to the new Item ID but still not link into any other parts of my data model. This creates a sort of "butterfly" model with Item dimension tables coming off both sides of the fact table.

One way to create a table of combinations is to join the COMBO table to itself several times. This runs the risk of exploding your memory requirements. I think this could be done better by LOADing the COMBO table again once and using complex peek() statements with sigma(2^n) fields, but that's more than I can work out here.

Hope that's some food for thought.

Jay

Basket analysis

Anonymous — Thu, 30 Apr 2009 01:40:46 GMT

I tried a little excersize - attached

Basket analysis

yblake — Thu, 30 Apr 2009 01:42:53 GMT

As far as I know, this frequent itemsets data mining is not (yet) implemented straight forward in QlikView, as there is no function to get support and confidence for itemsets combinations.

Combinations calculations can be done in the underlying database using APRIORI algorhytm :

ORACLE :

http://download.oracle.com/docs/cd/B28359_01/datamine.111/b28129/market_basket.htm#BABEEGCB

SQL Server :

http://msdn.microsoft.com/en-us/library/cc280428.aspx

Basket analysis

Fri, 01 May 2009 13:46:13 GMT

Thank you for your thoughts about this solution.

I think I will give it a go with Michales excellent solution. At least i think this will point me in the right direction. The only issue so far, is the performance as we currently have a base of more than 60.000.000 (with salestransactions in our base.

Hopefully QlikTech can make an easier solution in future releases of QV.

Have a nice weekend.

/Martin

Basket analysis

Fri, 01 May 2009 14:45:32 GMT

Jay:

Does that mean that your solution is not suitable for presenting the most popular combinations? Or do I miss something?
At this moment I use a simple macro to achieve the same result, without creating an extra table in my reload. You first need to select a product and then press a button to see the related products based on transaction or customer.


Sub RelatedProductsTransaction
 ActiveDocument.Fields("Transaction_ID").SelectPossible
 ActiveDocument.Fields("Product_ID").SelectExcluded
End Sub
Sub RelatedProductsCustomer
 ActiveDocument.Fields("Customer_ID").SelectPossible
 ActiveDocument.Fields("Product_ID").SelectExcluded
End Sub

Basket analysis

Anonymous — Fri, 01 May 2009 19:04:58 GMT

Thanks Martin,
I don't think my file is a solution, just trying an idea. Possibly it could be developed into a real solution even in the current QV versions.

Regards,
Michael

Basket analysis

Sat, 02 May 2009 00:02:11 GMT

Basket analysis determine what products customers are buying at the same time or at different times.

Provides opportunities to cross-sell and up-sell through relevant product recommendations.

In our case, we created a simple qv chart (pivot table) with time - product and customer dimensions and analyze what customer has bought.

Not sure if this helps, but it could give some directions.

Regards

Eraaj

Basket analysis

Sat, 02 May 2009 01:56:20 GMT

Mark,

In my implementation it does determine the frequency of each 2-combination of items. I haven't had the need to do it for 3 or more combinations.

Basket analysis

Sat, 02 May 2009 02:29:09 GMT

Jay,

If your implementation can determine the most popular combinations of 2 product I would love to here how you solved this. Can you share it with us?

Regards,

Mark

Basket analysis

swuehl — Sun, 15 May 2011 21:34:36 GMT

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

Re: Basket analysis

JonnyPoole — Fri, 18 May 2012 15:25:24 GMT

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