Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

White Space

reginawhelan
8 - Asteroid

Hi, I am having a hard time removing White Space from all the columns within my flow. I have tried the following:

- Dynamic Rename Tool - trim([_currentfield_])

- Data Cleansing Tool - Remove Unwanted Characters: Leading and Trailing WhiteSpace

- Multi-Field Formula - trim([_currentfield_])

 

The following chart shows how the data could be and my desired results:

 

OriginalDesired 
   Hello Hello
Good   DayGood Day
Thanks  (with white space at the end)Thanks

 

Does anyone have any ideas on how to remove unwanted white space?

8 REPLIES 8
JosephSerpis
17 - Castor
17 - Castor

Hi @reginawhelan I mocked up a workflow let me know what you think?

CharlieS
17 - Castor
17 - Castor

Hi @reginawhelan 

 

I would suggest using two functions to achieve this: Trim( and RegEx_Replace(. Trim you already mentioned, will remove the whitespace at the beginning and end. To replace repeated whitespace within the string, we can use RegEx to achieve this. The RegEx pattern to identify one or more space characters is "\s+", so if we replace those occurrences with just a space character " ", then we've eliminates repeating whitespace characters within the string. 

 

We can put both of these expressions together in one formula:

 

Trim(REGEX_Replace([Original],"\s+"," "))

 

Give that a try and let me know if that works for you. 

reginawhelan
8 - Asteroid

Thank you. It works using the Formula function but not a Multi-Field Formula. Do you know a quick way to add this formula to 97 columns? 

JosephSerpis
17 - Castor
17 - Castor

Hi @reginawhelan got it work using the multi field tool I've attached my workflow.

reginawhelan
8 - Asteroid

Thank you! Works perfectly.

 

MichaD
8 - Asteroid

 

Solved perfectly, but I was wondering if it wouldn't have worked with the Data Cleansing Tool, too?

If I tagged "Tabs, Line Breaks, and Duplicate Whitespace" in the "Remove Unwanted Characters" section of the tool (in addition to the standard "Leading And Trailing Whitespace"), I get the same results.

 

grafik.png

 

@JosephSerpis , @CharlieS , do you have any reasons for not choosing this "basic" tool? Just curious... ; )

 

JosephSerpis
17 - Castor
17 - Castor

The data cleanse tool does not have dynamic or unknown fields option so if additional fields are added you would need to manually select them. The Data cleanse tool is one of the worse performing tools in Alteryx so using regex with the Multifield tool is a more efficient solution and allows it be dynamic if the data expands. 

MichaD
8 - Asteroid

 

Thanks for the quick response, Joseph.

These are indeed good reasons which I was not fully aware of : )

 

Regards,

Michael

Labels