Dynamic Suffix - renaming existing columns

[MWoelfels]

Hi there,

I have monthly inputs including a date in the file name, say "20240906" for YYYYMMDD. During import, I chose the "import filename function", extracted the year& month in a new field [Extracted Date] and played a little with cross tab / transpose to get "202409" either as a column or a value.

Also, this input file contains columns such as "Name", "Amount" and "Number".

What I need to do is to dynamically add the extracted date as a suffix after the field names. Result should be "Name 202409", "Amount 202409" and "Number 202409", while in October, the result should be ""Name 202410".

Using "dynamic rename" tool and "Add suffix" only allows to enter a static value followed by my decision to add this value as a prefix or suffix. But I need to enter the value dynamically, here dependent on the date in the filename.

Other solutions using "Take Field Names from Right Input Rows" sound great, however I struggled to implement a proper solution.

Even with using cross tab for the FileName/extracted date and trying to use a multi-row formula creating new values with "[Name] + [Exctracted Date]" and then transpose it as my final columns failed.

Any suggestions on dynamic suffixes?

Thank you in advance,

[Carolyn]

---

Even with using cross tab for the FileName/extracted date and trying to use a multi-row formula creating new values with "[Name] + [Exctracted Date]" and then transpose it as my final columns failed.

I'm surprised this didn't work. This is the method that I do when I want to do what you're describing. 

See attached for what I did with the Cross Tab / Transpose method. I didn't need to use a Multi-Row Formula but could use a regular Formula Tool so maybe something about how your data is situated that's why yours isn't working

1. RecordID Tool so I can get everything back into the same order
2. Tranpose with Key Columns = RecordID and the Extracted Date
3. Formula Tool to modify the "Name" column as "Name" + " " + "Extracted Date"
4. Crosstab Tool. Grouped by Record ID. Column Headers = "Name" and Values = "Value"
5. Optional - Dynamic Replace to remove the "_" so "Amount_202409" becomes "Amount 202409"
6. Sort to put it back in the same order
7. Select to remove the Record ID

Edit: Revised option added to the workflow. One of the things that happens with the above method is you end up with the columns in alphabetical order (Amount then Name). If you add a Multi-Row Tool to assign a Column Order number before you start manipulating the Name and then Cross Tabbing, you can then have it go back to its original order. The Tools in the red boxes are different than in the first path. 

I use this trick when I need to do something like you're describing but my end users get upset if the columns are in a diff order than they were on the input.

If my workflow doesn't help, can you attach your workflow and some sample data? 

[CoG · Accepted solution]

Here is a variation that utilizes the "Take Field Names from Right Input Rows" (by making use of a handy Developer Tool (Field Info Tool):

I utilized @Carolyn 's very useful sample input. Hope this helps and Happy Solving!

[Carolyn]

@CoG Well that's stupidly simple. All this time I was Cross Tabbing and Transposing and I could've just done that! Brilliant! 👏👏

[MWoelfels]

Thank you for the fantastic support. Both solutions work perfectly.