Unique Values in a Field

[smoskowitz]

Hello --

Is there a way to write a regex statement to pull out unique values in a field?

If I have:

UK101, UK101, GA199, GA234, US100, US100

And I want to return: UK101, GA199, GA234, US100 -- how would i construct a regex to do this for me?

Thanks,

Seth

[PeterP · Accepted solution]

Hi @smoskowitz,

When you refer to a field, do you mean that the following values are within the same column? If so, you could simply use the unique tool on the field. 

If you meant that your values are comma delimited inside of a single cell, you could also use the text to columns tool to parse the cell into multiple rows and perform a unique on that field as well.

[jdunkerley79 · Accepted solution]

I'd probably go with the split and unique approach but:

REGEX_Replace([Field1], "\b(([A-Z0-9]+),\s*)(?=.*\2)", "")

Will do it in a single regex

[KRBergen]

I have a similar challenge, but the values that need to be parsed/identified as unique, are 1 to 3 characters long and are numbers (string) between 0 - 9,  For example, 9, 13, 344, 9, 18, 13, 344, 452 should yield: 9, 13, 18, 344, 452.  Since total record/row count is 100k+, I think the RegEx/Replace is the desired approach, but this expression didn't work in my case: 

REGEX_Replace([Concat_Group], "\b(([A-Z0-9]+),\s*)(?=.*\2)", "")

My "Column to Parse" is labeled "Concat_Group" column, which I changed in the original expression (was [Field_1] ).  Are there any other edits needed in this expression to yield the example shown above?

Thanks,

Kevin