Looking to Populate Field that will Change Every Month

[aplewis]

Hello, 

I'm attempting to populate a field with data from another source (move data from "Count" field into "Retired in July 2022" field), but the workflow needs to be dynamic so that each month the data is populated into the new column (i.e. when the workflow is run in September, the data will be moved into a column called "Retired in August 2022", which will have already been created). The simple formula tool I have works to fill the data the way I'd like it to, but is not dynamic as I specify the column to move the data into.

Is there a way that I could have Alteryx automatically populate whatever column is ahead of "Total Retired?"

Pictures attached. Thanks! 

[Robin_McIntosh]

If I understand correctly, you want the new columns to be added like this, before the Total Retired.  Add a select tool after the formula and move the *Unknown field up above the Total Retired column.  Then any new columns added by a formula will automatically be in front of Total Retired instead of after.

[aplewis]

The issue is not creating the new column, it's populating that column with data from the "Count" field, which will automatically update each month. The workflow is already set to create the new column, as it has for July in the images attached, but how do I then fill that new column with the data I need in a way that will also automatically change each month?

[Robin_McIntosh]

@aplewis  - Are you keeping all of the monthly columns and adding the new column?  Or dropping the prior months and only keeping the new month?

[aplewis]

Keeping all of the monthly columns and adding data for the latest month. The way the workflow runs right now, each month a new blank column will be created with the appropriate month name attached. Then, I need to find a way to dynamically move the data that exists in the Count field into the latest month's field. The select tool downstream serves to remove the Count field from the final table after the newest month's column has been populated with that data.

[SPetrie · Accepted solution]

This may not be the most efficient, but I think it will do what you want. Use a field info tool to grab all the fields and then sample the last 3. (Newest, total, and count). Then sample the first to get the newest column. A formula tool to setup an expression and an dynamic replace tool to update the formula. 

If I add another column before TotalRet, it will be the one that gets the count instead.