Regex or Formula tool

[AustinRiggs94]

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.

[danrh · Accepted solution]

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')

[MarqueeCrew · Accepted solution]

@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

[MarqueeCrew]

@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

[Ladarthure]

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]

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]

yes

[danrh]

@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]

Thanks everyone, Ya'll are the best.

[MarqueeCrew]

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