Dynamic Reordering of Columns

Question

Hello, I have a report that I run that has has multiple columns for rolling next 12 months. These columns must be in a specific order (as per image 1)

Each month when I run the report, there will be an additional column due to the addition of a new month.

The new months' columns end up being all at the bottom when I run the flow (as per image 2).

Is there a way to make the columns be in the same order as above?

Thanks!! any help will be greatly appreciated.

alteryx1.png

alteryx2.png

rzdodson · Answer

@teeefarnee: historically when I have run in to these use cases, I would typically put a RecordID tool pretty close to the beginning of the workflow, and then transposing the data set so that the column headings appear in a newly created Name field, their associated values appear in a new Value column, and then I would create a Key field using a Formula tool that is directly referencing the fields in the name (e.g. If [Name] = Safety Stock_2024_05 then 2 elseif [Name] Final Safety Days Supply_2024_05 then 3, etc.).

After this is accomplished, you can set up a Cross-Tab/Dynamic Rename combination. The beauty of this approach is that since we created the Key field, we can use it in the "Change Column Headers" selection in a Cross-Tab to get the exact order of the fields we are wanting. From there, the Dynamic Rename can take the headers from the first row of data to mirror the original order we are wanting out of the final output.

Hope this helps! :)

01. Solution.yxzp