Upgrading Server to version 22.1? We’ve enabled the faster AMP engine by default. Read about how these system settings changes might affect you.

2022-05-24 Updates: Login: If you are facing issues with logging in, please try clearing all your cookies or trying another browser. We have a fix being deployed on the evening of 5/25. Email: If you're not seeing emails be delivered from the Community, please check your spam and mark the Community emails as not junk. Thank you for your patience.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Identifying association sales for market basket

realdaniel1989
7 - Meteor

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

 

realdaniel1989_0-1626186844401.png
realdaniel1989_1-1626186862493.png

 

 

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

5 REPLIES 5
mceleavey
17 - Castor
17 - Castor

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.

 

mceleavey_0-1626199711450.png

 

The macro looks like this:

 

mceleavey_1-1626199746807.png

 

And the output looks like this:

 

mceleavey_2-1626199772453.png

 

I hope this helps,

 

M.

 

 

 

 

 

 

 



Bulien

realdaniel1989
7 - Meteor

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

realdaniel1989
7 - Meteor

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

mceleavey
17 - Castor
17 - Castor

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.



Bulien

realdaniel1989
7 - Meteor

Hi @mceleavey ,

 

Thanks for taking the time to help me out here.  Really appreciate it. 

Labels