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

Can multiple functions be combined in 1 Formula or Multi-Field Formula?

abovino
7 - Meteor

Hi,

 

Apologies if this question has been asked many times before but I tried googling and was not able to find an answer.  Also, I am new to Alteryx so I may be asking a dumb question.

 

I am trying to use 2 RegExReplace functions and 1 Trim function on 5 fields (Same fields for each function) in one Multi-Field Formula.  Is it possible to combine these functions or do I have to use the Multi-Field Formula tool 3 times?  I'm not sure if my 2 RegExs can be combined into one so that's why I have 2 separate.

 

Here is a data sample:

 

DateDistributorSalesRepCustNameCustCity
2/7/17    ACME                Joe Schmoe ?Smith LLC

New      York

2/7/17Parts    Company      Dave    Smith   Engineers Inc. 

-Albany

 

And here is the outcome I need:

 

DateDistributorSalesRepCustNameCustCity
2/7/17ACMEJoe SchmoeSmith LLC

New York

2/7/17Parts CompanyDave SmithEngineers Inc 

Albany

 

I tried using the Multi-Field Formula but I have not had any success:

 

Method 1: Did not remove white extra white spaces between words (Maybe my RegEx is wrong??)

// Removes all special chars
REGEX_Replace([_CurrentField_], "[^\w\s]", "") +

// Removes leading and trailing white space
Trim([_CurrentField_]) +

// Replaces extra whitespace between words with 1 space
REGEX_Replace([_CurrentField_], "\s{2,}", " ")

Method 2: Using || made all of my columns contain nothing but 0's

// Removes all special chars
REGEX_Replace([_CurrentField_], "[^\w\s]", "") ||

// Removes leading and trailing white space
Trim([_CurrentField_]) ||

// Replaces extra whitespace between words with 1 space
REGEX_Replace([_CurrentField_], "\s{2,}", " ")

Method 3: Using && had the same outcome as method 2, all columns contain 0's

// Removes all special chars
REGEX_Replace([_CurrentField_], "[^\w\s]", "") &&

// Removes leading and trailing white space
Trim([_CurrentField_]) &&

// Replaces extra whitespace between words with 1 space
REGEX_Replace([_CurrentField_], "\s{2,}", " ")
2 REPLIES 2
JoeM
Alteryx Alumni (Retired)

Well done! All you wrote was right on but just wasn't able to get it organized correctly within the multi-field formula. Rather that using "+" to add multiple functions together, you can nest them within each other. In the solution, you will find your exact logic re-arranged in the multi-field formula. 

 

Consider an example where I want to add 2+2 and multiply the result by 4. We can write it  "4(2+2)".  Now for a string that I want to Uppercase and trim, I would write: "uppercase(trim(field))".

 

Also, this job is a snap with the Data Cleanse Tool! Check out the attached solution. I hope this helps!

abovino
7 - Meteor

Thank you, Joe!

Labels