How to convert data into transaction format for Market Basket Analysis

[prakashatutd]

Hello:

I will appreciate any pointers / examples to convert store sales data into Transaction data to be able to run Market Basket Analysis in Alteryx.

Attached is a sample file and an example workflow. The output has commas at the end. How do I remove commas?

Thanks

[Carolyn]

Hey! For things like this, Transpose is your friend. It's an easier way to accomplish what you're trying to do without the excess commas at the end

1. Use Transpose to change data for rows to columns
2. Remove rows where the Value is null
3. Use Summarize to concatenate the remaining ones, grouped by Trans_ID
4. Optional: Sort by Trans_ID to get it back into its original order 

[Carolyn · Accepted solution]

Alternately, throw a TrimRight around your Itemset formula and set it to remove all commas on the right

I like the Transpose since it scales better. Each time you add new fields into your Transactions (Field_6, Field_7, etc), you'll need to update your Formula. Whereas the Transpose will just handle it. 

[prakashatutd · Accepted solution]

Many Thanks for this excellent approach

[prakashatutd]

Many Thanks!

Appreciate guidance and sharing the formula.