Hi,
I'm working on a market basket analysis but I can't seem to figure out for to obtain the associated sales and number of transactions with a particular association
Attached below is the sample
Summary on the attached file:
1) There are 2 transactions with multiple items purchased
2) Stock code AA200 wasnt purchased for transaction 2, therefore association for stock code AA200 will only contain 1 transaction as it didnt appear in the 2nd transaction
Hope someone can help me with this.
Thanks in advance
Solved! Go to Solution.
Hi @realdaniel1989 ,
This was an interesting one.
I've build this in stages, the first stage across the top uses the MB Affinity tool, which determines the number of basket associations between codes.
I then built a macro which loops through each Stock Code and transaction number and creates an association using the append tool between all combinations. This allows me then aggregate up to each unique combination (or at least unique in terms of primary and secondary code as per your required output).
I then joined the two phases together and created the desired output.
The macro looks like this:
And the output looks like this:
I hope this helps,
M.
Hi @mceleavey ,
Thanks for taking the time to look into the problem that I was facing.
Do you mind sharing with me the zip file because when I opened it the macro wasn't present.
Thanks again
Regards,
Daniel
Hi @mceleavey
I've replicated the macro and it's a valid solution but I'm concern with the append tool.
It generates too many records because some transactions are pretty big.
Do you happen to know if there is an alternative to the append tool?
Thank you so much for helping out
Hi @realdaniel1989 ,
unfortunately, what you're trying to do requires all combinations to be identified.
I've attached the original workflow with the macro zipped. This should create only the combinations per transaction number, which means it will only create the combinations that you need.
With a large dataset this will take some time to run as you are literally trying to create every possible combination in the set, there's not really a way around this as it's what you're trying to achieve.
Give this a go and let me know.
M.