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!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

New summary function request: concatenate unique strings

In the summary tool, we often use the summary tool to concatenate strings - we love this functionality.

 

However, we would also like to be able to concatenate just the unique values of strings. This could be done if we ran the preceding text field through the unique tool first, and then concatenate. But when we are doing this for multiple text variables and when we need to summarize other types of data at the same time, this becomes a very un-natural combination of joins & macros. 

 

Thanks for considering,

Jeremy

12 Comments
MarqueeCrew
20 - Arcturus
20 - Arcturus

Is order important?  Example:

 

A,B,C,A

A,A,B,C

 

I've got a formula that will dedup these as:

 

B,C,A

A,B,C

 

It works with words too:

 

Any, Bad, Character, Any

Any, Any, Bad, Character

 

Try this:

 

trim(regex_replace([field],"\b(\w+),(?=.*\b\1,?)",""))

Philip
12 - Quasar

Thanks for this code, MarqueeCrew! I overcame the order issue by sorting prior to the summarize. The code gave exactly what I needed.

bb213
8 - Asteroid

Thank you @MarqueeCrew.  I needed this. 

WholesaleAndy
5 - Atom

Thank you @MarqueeCrew for sharing. Hoping to see if you can explain or help expand this solution.

 

I am trying to create the same distinct list, but I am hoping to handle any characters (including whitespace) rather than just letters and numbers. For example, **Test**,**Test**,**Test**.

 

Is this feasible with Regex? thank you for reading!

kenjaleamruta
5 - Atom

I would like to see this functionality to Summarize tool. Meanwhile, you can use Unique tool to filter unique combinations of the field you would like to group by and value to concat. Then use Summarize with group_by.

 

Unique variables ---> Summarize with a group by and concat

 

WholesaleAndy
5 - Atom

@kenjaleamruta, yes, this is a solution for when you have 1, maybe 2 fields. As the number of fields in which you are hoping to do a unique concat on, the more complex it becomes.

bb213
8 - Asteroid

Unfortunately, this solution does not work for ")".  In other words, "),),),)" will not become ")". Any way to tweak it?  

MHaak
7 - Meteor

What you could try is using the summarize tool twice to get the results you want.

First you group on the data you want to (unique) concatenate and in the summarize tool after that you can actually concatenate the value.

bb213
8 - Asteroid

@WholesaleAndy maybe you've found an answer already, but this should work for you: 

 

trim(regex_replace([field],"\b(\D+),(?=.*\b\1,?)",""))  

Community_Admin
Alteryx
Alteryx
Status changed to: Inactive