Need help sorting header names

[averyn]

I'm pulling a sales file that comes in with headers:

Item SKU #, Item Description, TY 2018 Week 1 [Date], LY 2017 Week 1[Date], % Chg, TY 2018 Week 2 [Date], LY 2017 Week 2[Date], % Chg,............

Unfortunately, the data source platform isn't flexible on how the data looks when pulling.

And I need 104 weeks worth of data for charting and the only way to get all 104 weeks is to pull last 52 weeks and comparable LY sales next to each other.

% Chg isn't a necessary column because that is a calculation that I can do later on. I cannot pull all 104 weeks of data without the % Chg column being "conveniently" added. 

My question is, is there a way to sort the columns in an ascending order so that it would be sorted A-Z than the earliest date to latest date. 

This will make it easier to do calculations for L52 week, L24 week, L12 week sales. 

Thanks for any help or ideas as I've been stuck on this roadblock for some time now!! 

[derekbelyea · Accepted solution]

This may be what you need

[rahul1011 · Accepted solution]

TRANSPOSE data.

Use the FORMULA tool to make the data appear in order in which you want it sorted e.g. 1_ for 1st, 2_ for 2nd column (or you can just pick the week num and year and prefix the data with it). 

Use CROSSTAB to create the pivot as required.

Use FIELD INFO in a separate flow to pick the column names that were present before the transpose and then use DYNAMIC RENAME to rename the fields again based on the data before the transpose.

After this, the columns will be sorted in the order you want, but will still have the same name.

A similar example below.

[tom_montpool]

There is a Field Sort macro that might come in useful:

Sort-Field-Names-Tool

The Dynamic Rename will also be useful.