topic Re: Market basket analysis in QlikView

Market basket analysis

john9inno — Mon, 20 Jul 2015 06:34:53 GMT

Hi all,

I have gone thorugh many of discussion and articles about Market Basket Analysis. (MBA)

all of them explans about how get MBA data for a seleted item or product.

However, I need to generate a full list that contains all items (about 200K SKUs) and 5 items next to it that shows highest co-occurence.

Can any one please give me a tip for this?

thanks in advance.

For below data, I need to generate the list that contains all Prod code, Associated Prod Code and frequency of Co-occurence.

LOAD * INLINE [

    Customer, InvoiceNo, Prod

    260690, 1, Tea

    260690, 1, Tea

    260690, 1, Fruit

    260690, 1, Fruit

    260690, 1, Fish

    260690, 1, Fish

    260690, 2, Tea

    260690, 2, Tea

    260690, 2, Fruit

    260690, 2, Fruit

    260690, 2, Fish

    260690, 1, Corn

    260690, 1, Corn

    260690, 1, Fruit

    260690, 1, Fruit

    260690, 1, Tea

    260690, 1, Tea

    260690, 3, Corn

    260690, 3, Corn

    260690, 3, Fruit

    260690, 3, Fruit

    260690, 3, Fish

    260690, 3, Fish

    260690, 1, egg

    260690, 1, egg

    260690, 1, egg

    221729, 3, Tea

    221729, 3, Tea

    221729, 3, Tea

    221729, 3, Tea

    221729, 3, Tea

    221729, 3, Tea

    3898439, 4, egg

    3898439, 4, Fruit

    3898439, 5, egg

    3898439, 5, Fruit

];

Re: Market basket analysis

tresB — Mon, 20 Jul 2015 06:52:04 GMT

Try to share your sample qvw. See:

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Market basket analysis

john9inno — Mon, 20 Jul 2015 07:28:08 GMT

please see amended question.

thanks so much for your kind comment.

Re: Market basket analysis

Mon, 20 Jul 2015 07:37:39 GMT

Hi John,

What do you mean by co-occurrence in this context, share the expected output so that we can help you.

Regards,

Navdeep

Re: Market basket analysis

tresB — Mon, 20 Jul 2015 07:41:34 GMT

Like in attached sample qvw?

Re: Market basket analysis

prashantbaste — Mon, 20 Jul 2015 08:23:17 GMT

I think there is some problem in your data as each record is repeated twice.

Now answering to your mail answer - You need to make buckets of all possible combinations of Items & then check those into each invoice if combination is there in order.

[* Technical Hint: Assigning number to each item & use of loops will help to achieve this]

The top 5 count of these buckets will give your desired Market Basket Analysis.

Hope this will be helpful for you.

Regards,

Prashant P Baste

Re: Market basket analysis

antoniotiman — Mon, 20 Jul 2015 08:51:58 GMT

Hi John,

can You provide, at least, the expected result ?

Perhaps, like this

Regards,

Antonio

Re: Market basket analysis

john9inno — Tue, 21 Jul 2015 05:31:10 GMT

Problem solved.

THanks all for your valuable inputs.

Product	Associate_product	Co_Occurunce ( In same invoice)
egg	Corn	1
Corn	Corn	2
Fish	Corn	2
Fruit	Corn	2
Tea	Corn	2
Corn	egg	1
Fish	egg	1
Tea	egg	1
egg	egg	3
Fruit	egg	3
egg	Fish	1
Corn	Fish	2
Fish	Fish	3
Fruit	Fish	3
Tea	Fish	3
Corn	Fruit	2
egg	Fruit	3
Fish	Fruit	3
Tea	Fruit	3
Fruit	Fruit	5
egg	Tea	1
Corn	Tea	2
Fish	Tea	3
Fruit	Tea	3
Tea	Tea	3