Hi All, After concat this is the column value I am getting. 1Apples, Oranges, Apples, Apples,Apples,Oranges2Kiwi,Banana,Banana,Banana,Kiwi How can I now show only unique values like grouping. Expected output 1 Apples, Oranges 2 Kiwi, Banana Thanks

Unique records in column rows

Hi All,

After concat this is the column value I am getting.

1	Apples, Oranges, Apples, Apples,Apples,Oranges
2	Kiwi,Banana,Banana,Banana,Kiwi

How can I now show only unique values like grouping.

Expected output

1 Apples, Oranges

2 Kiwi, Banana

Thanks

Developer Tools

Common Use Cases

Accepted answers

JDong

I refer to this helpful post

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Remove-Duplicates-within-a-cell/td-p/6701

And the below regex works perfectly fine

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

The only issue is some values have commas only like the below

,,,,,,,,,,,,,,,,,,,,

,,,,,

Is there anyway I can remove the commas only in rows where there is no text like Apples, Oranges?

Thanks

All comments

RolandSchubert

Hi @JDong ,

you could split the values to rows using the Text to Columns tool, select unique values only using the Unique tool and "re-concatenate" using the Summarize tool.

I've attached a sample workflow.

Let me know if it works for you.

Best,

Roland

UniqueValues.yxmd

JDong

Thanks Roland.

The actual data has 40 columns and not sure if I need to select all the columns in the unique field ?

Also some rows where no values are available the data is in the below formats and want to clean up the delimiters

Apples,

,,,,,,,,

,Apples,,Oranges

,Oranges,,,,,Apples

danilang

Hi @JDong

Since you mention that you get the duplicates after concatenation, you should probably remove them before you concatenate. Your data before is probably this

Use a Unique tool to remove the duplicates before the summarize tool

Giving you

Dan

Unique.yxmd

JDong

Thanks or something like this formula field also worked before the summarize tool

IF Trim([field])='' THEN Null()
ELSE [field] ENDIF

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335