Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Grouping concatented strings

smostafa
5 - Atom

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.

 

 

 

5 REPLIES 5
AlexKo
Alteryx Alumni (Retired)

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

Alex Koszycki
Program Manager, Community Platform
MarqueeCrew
20 - Arcturus
20 - Arcturus

I think that this is what you want:

 

Capture.PNG

 

I hope that it helps you.

 

Thanks,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
AlexKo
Alteryx Alumni (Retired)

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.

 

Capture.PNG

Alex Koszycki
Program Manager, Community Platform
MarqueeCrew
20 - Arcturus
20 - Arcturus

Very interesting problem.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
smostafa
5 - Atom

Thank you Mark and Alex for your help with this!

Labels