Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Removing Excess Comma's in an Concatenated Field

Highlighted
11 - Bolide

Hello --

 

I am working on a project and have a challenge that is eluding me. I have concatenated about 7 fields using a comma (,).

 

1) ,,,,CBC500,,,,CBC501,,

2) ,,,,,CBC100,,,,,

 

How do I keep a single comma to delineate, but remove any unnecessary commas. So for item 1, I want to see:

 

CBC500,CBC501

 

Thanks,

Seth

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
You could try:

Regex_Replace([field],",{2,}",',')

That gets rid of repeating commas. To get rid of the first and last commas:

Trim(Regex_Replace([field],",{2,}",','),",")

Does that work for you?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
11 - Bolide

Wow! That worked! Now ask me if I understand why it worked.

 

Thanks,

Seth

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Regex is for pattern matching. It is well worth investigating. We are looking for a comma that exists 2 or more times. That pattern is: ,{2,}

It could also be written as: ,,+

When alteryx finds it, it will replace it with the string of a comma.

Trim will remove leading and trailing white space by default. You can also specify a string to remove.

I combined the two functions in a nested statement.

Sorry for the iPhone short explanation, but I'm answering mobile.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
11 - Bolide

This is genius! Thank you!

Highlighted
8 - Asteroid

could the same formula be used to get rid of multiple words?  I have a concatenation which ends up with OCC,OCC,,,,OCC, . I'd like to get rid of the commas (which your regex does, thank you), and I'd like to get rid of the repeating OCC.  Would it be something like \w(OCC){2} in the statement instead of ,{2,}.

Highlighted
9 - Comet

You're good @MarqueeCrew , very, very good! 

Labels