Only Keep Max Date Field Name in a dynamic Data set

[paulwini]

Hi Everyone,

I have a data set that is structured as follows: 

This data will be updated monthly to include a new column, which will contain the most recent month's data. I'm looking to create a workflow that will only include the most recent Sales month column. It does need to be dynamic enough to allow for the workflow to run each month and only keep the most recent month data that has been added.

The desired output is as follows: 

I attached the data in this post. This data includes an input tab and also a desired output tab. I made some progress on a workflow, but I can't tell if my workflow is headed in the right direction or needs to be redone entirely. The output is close but not what I need.

Any help is greatly appreciated.

Thank you,

Paul 

[atcodedog05 · Accepted solution]

Hi @paulwini 

Here is how you can do it.

Workflow:

1. Using transpose to convert columns into row. Once converted the columns will be in order i,e last/max date column will be last row of each ids

2. Using sample tool to get only last row for ids

3. Using crosstab to convert back to table.

4. Using dynamic rename to fix the column name issues.

Hope this helps : )

[Luke_C]

Hi @paulwini 

Here's my pass at this:

• Transpose
• Convert to dates
• Find max date, join back to get just records for the max date
• Cross tab back