Dynamic Suffix - renaming existing columns

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,

Best Practices

Dynamic Processing

Accepted answers

CoG

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!

Sample Workflow.yxmd

All comments

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

RecordID Tool so I can get everything back into the same order
Tranpose with Key Columns = RecordID and the Extracted Date
Formula Tool to modify the "Name" column as "Name" + " " + "Extracted Date"
Crosstab Tool. Grouped by Record ID. Column Headers = "Name" and Values = "Value"
Optional - Dynamic Replace to remove the "_" so "Amount_202409" becomes "Amount 202409"
Sort to put it back in the same order
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?

Cross Tab Transpose.yxmd

CoG

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!

Sample Workflow.yxmd

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.

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703