community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

Removing Excess Comma's in an Concatenated Field

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

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.
Bolide

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

 

Thanks,

Seth

 

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
Bolide

This is genius! Thank you!

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,}.

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

Labels