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

RegEx Replace Function in Formula Tool

ZoeM
8 - Asteroid

Hello Community.

I have searched the blogs and posts to see if I can find an example for my situation, I want to wrap the text in one of the fields in my data set. I have 7 unique categories that I need the text wrapped before it feeds into the QVW data model and most posts are referencing using the RegEx Replace formula within the Formula Tool.

 

Could someone help me write the expression, and would I have to have 7 different expressions since I have 7 unique categories? 

 

Field Label - Content Issues

1. Lessons Learned - Constraints

2. Main Causal Factor - Primary

3. Strategic Metrics Health - Population

4. Program Health Review - Regional

5. Lack of Resources - Program

6. Timing Change - Program

7. Direction Change - Global

 

Any help is appreciated,

 

Thanks!

 

11 REPLIES 11
neilgallen
12 - Quasar

@ZoeM it would be helpful if you would include an example of your input data and your desired output. That way folks here can best determine the most efficient approach.

BenMoss
ACE Emeritus
ACE Emeritus

My understanding is that you simply want to replace the column value if it matches the field label, and replace that with the statement on the right the 'Content Issues'.

 

This is relatively straight forward using the find and replace tool and i've attached an example of how you can achieve it.

 

Though i may not be understanding the problem completely, let me know if my solution is way off the mark!

 

Ben

 

 

ZoeM
8 - Asteroid

I have attached a sample of what the data is, and what the data result should be.

I would like to wrap the text in the Content Issues Column so that I have two strings in one cell.

 

Hope this helps

BenMoss
ACE Emeritus
ACE Emeritus

In your sample data both the values are already in the same cell? I assume you want to create a new line, but what criteria is there for pulling the word onto a new line, we could use after a certain amount of characters.

 

Alternatively we could use the dash.

 

To do the latter, you can use a replace statement.

 

replace([Content Issues],"-","-
")

The new line in the statement is purposeful.

ZoeM
8 - Asteroid

Unfortunately not all Content Issues have a dash in the field name.

 

My initial thought was to use the find and replace tool and see if I could replace the original field entry with the new concatenated entry, but not sure how to include either a formula tool or RegEx tool to concatenate the Content Issues Field. I feel I am over-complicating the process and should be simple enough.  

BenMoss
ACE Emeritus
ACE Emeritus

I'm not sure I follow this statement 'concatenate the Content Issues Field'

 

In the sample data you sent they are already concatenated.

 

ZoeM
8 - Asteroid

Yes. The data is already in one string as it comes into Alteryx.

 

I cannot change the data source, but would love to create the two string lines in one cell so that it does not affect the formatting in QVW. 

Claje
14 - Magnetar

Here's a really brittle regular expression using the RegEx tool.


I think you could apply the same in a formula, but I would recommend finding a more consistent way to identify these records

 

Regular Expression:

(Lessons Learned|Main Causal Factor|Strategic Metrics Health|Program Health Review|Lack of Resources|Timing Change|Direction Change).*(Constraints|Primary|Population|Regional|Program|Global)

Replacement Text

 

$1 \n$2


Config:

claje_RegexToolConfig_Newline.PNG

 

Note that this will only work with the seven prefix categories listed and the 6 suffix categories listed, and it will match for ANY prefix/suffix combination, even one not in your initial categories.

ZoeM
8 - Asteroid

Thanks for that example Claje,

I am writing the regular expression and one issue I am facing is that one of the field line items is only one word and would not need a carriage return. However, can I still use ' ' in the second part of the expression to signify a blank carriage return?

 

E.g

(Lessons Learned - Constraints|Resources|Main Causal Factor - Primary).*(Constraints|' '|Primary)

Labels