Can multiple functions be combined in 1 Formula or Multi-Field Formula?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
Date | Distributor | SalesRep | CustName | CustCity |
2/7/17 | ACME | Joe Schmoe | ?Smith LLC | New York |
2/7/17 | Parts Company | Dave Smith | Engineers Inc. | -Albany |
And here is the outcome I need:
Date | Distributor | SalesRep | CustName | CustCity |
2/7/17 | ACME | Joe Schmoe | Smith LLC | New York |
2/7/17 | Parts Company | Dave Smith | Engineers 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,}", " ")
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you, Joe!
