Dynamic Rename Formula Set-up

[joeltrudel]

I have three files that come from one of our vendors that I've successfully combined into one output. However, the column headers are for Sales within a given week (i.e. Jan 05 2020 - Jan 11 2020) and I need each of those columns to take on a "date" property so that I can bring this data into Tableau and create a line chart that shows sales throughout the year (end goal).

Current Workflow:

What the data looks like after the three tabs are combined; with the Find and Replace tool. 

I transposed the data so that all column headers are under the column "Name".

Now, what I'd like to do is parse out ONLY the First date in the column (i.e Jan 05 2020 - Jan 11 2020) to become ONLY Jan 05 2020 in a way that I can then convert it to be a date assuming using the select tool; not text. They are weeks, but theoretically I only need one of the dates.

Please let me know if you have any ideas or can help with the formula and set up I'd use with the Dynamic Rename. And then how I'd finish the workflow to bring the date in to my final output.

Thanks!

[kelsey_kincaid]

@joeltrudel You might try the 'Text to Columns' tool separating your 'Name' field into two separate fields, delimited by hyphens. You'll have to clean up some white space and rename some fields, but the Text to Columns tool should get you started.

[joeltrudel]

thanks Kelsey! I actually think this might work- I'm running the workflow now and included at data cleanse tool to clean up the white space. Do you have a possible solution for turning the first column (you have as Name 1) into a Date format? Will it work with just a select tool, or do I need to do some other configuration?

Appreciate the follow up!

[Luke_C]

@joeltrudel 

Once its split to a column you'll need to parse it into alteryx datetime format. A select tool alone can't do that. If you use a formula tool and put

datetimeparse([Field Name],'%b %d %Y') it should convert it into date format.

more info on datetime functions: https://help.alteryx.com/current/designer/datetime-functions

[kelsey_kincaid]

Hi @joeltrudel , 

@Luke_C 's solution would work great. You can also use the 'DateTime' tool to convert it to a date if you find that easier. An example of the configuration is below.