Cleaning a column in alteryx

[NadaH]

Hello everyone,

I have an issue trying to clean the a column on my workflow.

I got this table and would like to clean column A by removing the occurrences

A	B	C
hi,hi,hi,hi,hello	1	45
ab,ab,an,an	2	54
de,bon,bon	3	32

I should have this result:

A	B	C
hi,hello	1	45
ab,an	2	54
de,bon	3	32

Thank you in advance for your help

[atcodedog05]

Hi @NadaH 

Here is how you can do it.

Formula:

TrimRight(REGEX_Replace([A]+",", "(\b\w+\b,)\1+", "$1"),",")

Workflow:

I am using regex for removing duplicate words.

Here is a weekly challenge which had a same kind of usecase https://community.alteryx.com/t5/Weekly-Challenge/Challenge-130-Is-There-an-Echo-in-Here/td-p/206817 

Hope this helps : )

[mceleavey]

Hi @NadaH ,

This is simply achieved by splitting the data to rows on column A using the comma separator in the Text to Columns tool.

Then you use the sum to group it creating a unique combination (or the Unique tool) then using the sum tool to group again by the second and third columns, and concatenating Column A separated by comma:

This gives the following:

Workflow attached.

Hope this helps,

M.

[NadaH]

Thank you for your formula.

When I use this formula it works on some rows like this one:

But it doesn't work here:

I have put the result on column newCCandOI , I should have "Hors scope" only one time

[atcodedog05 · Accepted solution]

Hi @NadaH 

Use this formula. I have adapted it.

TrimRight(REGEX_Replace([A]+",", "([\w\s]+,)\1+", "$1"),",")

Workflow:

Hope this helps : )

[NadaH]

Thank you! It works 

[atcodedog05]

Happy to help : ) @NadaH 

Cheers and have a nice day!

[NadaH]

I just noticed that it didn't work on this example,

Can you tell me what I can add to the formula .

Thank you

[atcodedog05 · Accepted solution]

Hi @NadaH 

Use this formula. Should cover most scenarios

TrimRight(REGEX_Replace([A]+",", "([^,]+,)\1+", "$1"),",")

Workflow:

Hope this helps : )

[NadaH]

Thanks , Can you explain me the logic behind this formula or where I can found ressources to understand it?