Grouping concatented strings

Hi,

I recently started using the software and was hoping someone might have some advice for a problem I'm running into when I try to group concatenated strings using the summarize tool. Basically, I started out with data that consisted of a row for every customer entry--with multiple entries for each customer, grouped by transaction ID, like so:

Cust A apples Jan

Cust A oranges Jan

Cust B peaches Jan

Cust C apples Feb

Cust C oranges Feb

I am interested in analyzing all the different combinations of purchases by month, on an aggregate level.

I seperated out the fields I was interested in, grouped my entries by unique customer identifier and then concatenated the purchase strings to get one row per customer summarizing their purchase per transaction. From there, when I tried to group and count based on the concatenation, I got duplicate entries because of subtle nuances in how the purchases were concatenated. Ex: differences in space, the ordering of purchases, etc...

Ultimately, I just want a simple tabulation by group combinations, by month. Any advice on this would be appreciated.

Transformation

Preparation

Accepted answers

AlexKo

Duplicate entries were being formed when a single customer had multiple purchases for an item being fed into the Group By. The Summarize tool isn't able to "Concatenate Distinct". Grouping first by Product and Customer removed the duplicate purchases, then finding the Combinations from this dataset resulted in a unique, ordered list. The tricky part is keeping track of the purchase counts properly.

smostafa and I worked out this logic in the below workflow. The first flow calculates an aggregate summary on combinations, while the second calculates a summary on the month-level.

new_workflow.yxzp

All comments

AlexKo

I'm not sure if I fully understand this - if you have time I'd be happy to WebEx and work through this with you. My email is akoszycki@alteryx.com

MarqueeCrew

I think that this is what you want:

I hope that it helps you.

Thanks,

Mark

possibilities.yxmd

AlexKo

smostafa and I worked out this logic in the below workflow. The first flow calculates an aggregate summary on combinations, while the second calculates a summary on the month-level.

new_workflow.yxzp

MarqueeCrew

Very interesting problem.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335