Concatenate Distinct values in 'Summarize' tool

[rohan_tibarewala]

We have 'CountDistinct' and 'Concatenate' options within Summarize tool. 

But 'Concatenate' displays all the instances of value for a Grouped field, this might include lot of duplicates.

It would be great to have an option like 'ConcatDistinct'.

For example - 

Group by 'Branch' and 'ConcatDistinct' Customer should result as Figure 1 instead of Figure 2 - 

Figure 1-

Figure 2-

While this is achievable in different ways currently with a set of tools, but it gets tedious when number of fields is large from which distinct values are to be captured. 

Thank you,

Rohan.

[haraldharders]

I just wanted to upload a very similar feature request. I would like following two sub-categories of this:

• Unique concatenate (preserving original order)
• Unique concatenate alphabetically ordered

Also, this feature should work also when doing multiple concatenates in the same Summarize tool.

[phoebe_kelley]

This would be AMAZING and save so many tools. Right now I have to do a bunch of separate summarize tools and then join them back. Please please please!

[phoebe_kelley]

Just to add my use case, which is usually many fields that I want to summarize but have to do separate summarize tools and then join back:

to go from this:

Branch	Customer	Supplier
A	Tom	Cole's Cookies
A	Tom	Sammy's Sandwiches
A	Jack	Sammy's Sandwiches
A	Tom	Alice's Alligators
Branch	Ned	Alice's Alligators
Branch	Ned	Bam's Bananas

to this:

Branch	CONCAT_Customer	CONCAT_Supplier
A	Tom, Jack	Cole's Cookies, Sammy's Sandwiches, Alice's Alligators
B	Ned	Alice's Alligators, Bam's Bananas

I have to use 4 summarize tools and a join:

I often have more than two fields, so I have to use even more summarize tools and a join multiple to get them all back together.

[haraldharders]

Dear Alteryx team. Is there any chance that you revisit your decision to not implement this feature? It would prevent me from such clumpsy and slow workflows:

[fmvizcaino]

Countless times I wanted to do this and needed to add a few additional steps 😑

[marcusblackhill]

I hope Alteryx plan to put that back in the table. Concatdistinct would save some good time of workarounds.

[RuHRoW]

This is a great suggestion and I hope they reconsider. Meanwhile, if your workaround involves more than one tool try this:

Concatenate all fields in the same Summarize tool and follow with a formula tool. All it takes is a fairly generic regex pattern.

Here's an example using the same data from @phoebe_kelley's use case above...

Here are the regex formulas used in the formula tool for each concatenated field:

REGEX_Replace(
	[Concat_Customer], //string
	'^([^\,]+?)(?:\,\1)*(\,(?!\1)[^\,]+?)?(?:\,\1|\2)*((?!\,\1|\2)\,[^\,]+?)?(?:\,\1|\2|\3)*((?!\,\1|\2|\3)\,[^\,]+?)?(?:\,\1|\2|\3|\4)*((?!\,\1|\2|\3|\4)\,[^\,]+?)?(?:\,\1|\2|\3|\4|\5)*((?!\,\1|\2|\3|\4|\5)\,[^\,]+?)?(?:\,\1|\2|\3|\4|\5|\6)*$', //pattern
	'$1$2$3$4$5$6' //replace
)

REGEX_Replace(
	[Concat_Supplier], //string
	'^([^\,]+?)(?:\,\1)*(\,(?!\1)[^\,]+?)?(?:\,\1|\2)*((?!\,\1|\2)\,[^\,]+?)?(?:\,\1|\2|\3)*((?!\,\1|\2|\3)\,[^\,]+?)?(?:\,\1|\2|\3|\4)*((?!\,\1|\2|\3|\4)\,[^\,]+?)?(?:\,\1|\2|\3|\4|\5)*((?!\,\1|\2|\3|\4|\5)\,[^\,]+?)?(?:\,\1|\2|\3|\4|\5|\6)*$', //pattern
	'$1$2$3$4$5$6' //replace
)

This pattern removes duplicates from a delimited list with up to 6 unique values.

Click here for an explanation of the pattern. This link shows the same pattern using a different delimiter.