How to programatically delete date fields?

[pupmup]

Hello everyone!

I've just received Alteryx and am jumping in and trying to blend together 7 reports that we get every week into one Tableau Extract. 

One of the reports is set up in a ridiculous fashion, with every week in the year set up as a column (/field I guess). The data in these columns then become non-zero as the year progresses. 

(See here for screenshot of two weeks with data and one week without)

This Alteryx workflow will be used every week - so I need to:

1) Strip the text from the column headers to just leave the dates

2) Automatically delete every column except the three headed by the date closest in the past to (today's date) - as if the Alteryx flow is run at the end of the week I don't want it finding the closest week in the future.

3) Rename the remaining column to "Sales volume" or whatever as appropriate.

(3) seems pretty easy, but I'm stumped on (1) and (2). 

Any advice welcome..thank you!

Sam

[jgo · Accepted solution]

HI @pupmup,

You'll probably want to transpose the data so that those date columns are down the rows instead of across the columns.

From there, you can use a, or a set of, tool to parse out the date only and convert it to a date field (Formula, Text to Columns, RegEx are a few tools that come to mind). Then you'd use a Filter tool to create a conditional expression to filter on the dates you'd like (DATETIMEADD() would be the function to create a dynamic reference).

Hope this helps, but if you're able to post an example of what the data looks like, I can probably mock something up for you pretty quickly.

[pupmup]

Hi @jgo!

Thank you for your help. So you're suggesting I pivot the columns into the data? That would be great.

I'm going to try it myself, but if you wouldn't mind creating a quick mock up it would be really appreciated! 

I've attached a sample of the data.

Thank you,

Sam

[jgo]

@pupmup,

Below is an screenshot of the solution (really for anyone else who stumbles upon this post), but attached is the workflow. You'll need to redo the input and point it to your source.