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 or Formula tool

AustinRiggs94
8 - Asteroid

Hi All,

 

The issue i am having is that i am trying to rename the field names to just the date (Take out Scheduled Hours Daily/Sum or Scheduled hours). The issue is, this report will be updated with new field names (dates) in the future. Is there anyway to write a formula/use Regex tool to just take the date, which is in parentheses, and use that as a field name replacement. Please see below for the field names. 

 

I thought about using the generic field names F1, F2, F3 etc., since the column placement won't change, only the name will. Am i on the right track? Please let me know, thanks.

 

Scheduled Hrs (Daily) (10/16/2018)Scheduled Hrs (Daily) (10/23/2018)Scheduled Hrs (Daily) (10/30/2018)Scheduled Hrs (Daily) (11/06/2018)Scheduled Hrs (Daily) (11/13/2018)Scheduled Hrs (Daily) (11/20/2018)

 

These dates are weekly for the next 6 months.

9 REPLIES 9
danrh
13 - Pulsar

RegEx is the way to go when there's a pattern, but the data changes. Toss the following in a Dynamic Rename tool and it should get you there:

RegEx_Replace([_CurrentField_],'.*\((.*?)\)','$1')

image.png

MarqueeCrew
20 - Arcturus
20 - Arcturus

@AustinRiggs94,

 

Use a DYNAMIC RENAME tool and select all columns (including Unknown).

 

regex_replace([_CurrentField_],".*\((\d{2}\/\d{2}\/\d{4}).*",'$1')

This will do the work for you in finding the date and renaming the columns.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
MarqueeCrew
20 - Arcturus
20 - Arcturus

@danrh,

 

You were quick, but if you test with "Scheduled (Daily)" as a field header, you'll get "Daily" as your output.  I was extra cautious about the dates.  Having said that though, I might make the formula edit as:

regex_replace([_CurrentField_],".*\((\d{1,2}\/\d{1,2}\/\d{4}).*",'$1')

This would catch "Scheduled (Daily) (9/1/2018)" if the 0's are missing on month or day.

 

Cheers,

 

Mark

 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Ladarthure
14 - Magnetar
14 - Magnetar

Hi,

 

to do that I would use a specifivc data stream to get the names you want, and then use a dynamic rename to have the good field names needed for your report.

 

Hope it helped!

AustinRiggs94
8 - Asteroid

Thanks for the quick reply everyone. Just to clarify, this would work for future reporting, as in the column dates changing (but not field placement). Main issue i was trying to a void was having to manually select every Date column, every time it was ran. Let me know, and thanks again everyone.

MarqueeCrew
20 - Arcturus
20 - Arcturus

yes

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
danrh
13 - Pulsar

@MarqueeCrew, that's true. @AustinRiggs94 didn't mention having a field where the last parenthesis was completely missing, but I guess that's a possibility. In that case, would it be better to grab the contents of the last set of parenthesis or simply leave the column header as is? It sounded like from the original post that all the fields would be more or less uniform.

 

@AustinRiggs94, yes, as long as you select "Dynamic or Unknown Fields" this will work as new columns are added without you having to manually select them.

AustinRiggs94
8 - Asteroid

Thanks everyone, Ya'll are the best.

MarqueeCrew
20 - Arcturus
20 - Arcturus

Thanks @AustinRiggs94!

 

You can have multiple solutions, so @danrh's suggestion too is a solution.  If in the future someone else adds a new or inventive way to solve the challenge, please do take the time to mark their solution(s) too.  Alteryx is always changing and there are many ways to solve a challenge.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels