require only last 5 columns date wise

Question

I have excel input file, i have total 6 columns 1st column contains field values and other 5 columns contains values date wise and i have built Alteryx workflow which give me output for particular date. Now i want to add Alteryx generated output to the right end side of the data in excel and when new column get added in excel file 2nd column should get remove automatically so in excel file only last 5 days data remains with 1st static column. also if same date data get added it should overwrite that column rather creating new column

Note : excel is the input as well as output file

SPetrie · Answer

You can use a field info tool to get your column names and parse the dates from them. Use a dynamic rename to update descriptions and a dynamic select to decide which columns to keep and it will always give the last 5 columns and whatever other columns you specify.

fields.yxmd

jesse0112 · Answer

I would probably handle this by reshaping the data rather than trying to manage columns directly. Convert the date columns into rows using a Cross Tab/Transpose approach, append the new day's results, remove duplicates based on the date (keeping the latest record if the date already exists), then sort by date and keep only the most recent 5 dates. After that, pivot the data back to columns and overwrite the Excel file.

That way you automatically get:

* only the latest 5 date columns,
* replacement of an existing date instead of creating a duplicate column,
* no need to hard-code column names as new dates are added.

This tends to be much more reliable than trying to dynamically delete the second column every time a new date is written.