Get Filename into columne header

Question

Hi, how can i rename a columne headline into my file name? i know i will need the "dynamic rename tool". here i use formular with: If contains([_CurrentField_], "spend") then >>filename<< else [_CurrentField_] endif ("spend" is the header i wanna rename) so how can i can give this funktion the infomation about the filename? i was thinking about 1) "output file name as field" in the input data - but then i have the information it in a new columne but how to give the if funktion this information? 2) funktion: "FileGetFileName(Path)" but how to get the filepath dynamic? Yes with the "field info tool" but same here - how to give this information to the if funktion in dynamic rename?

NicoleJohnson · Accepted Answer

Try a Transpose + Cross-Tab tool! See example attached (version 11.7). By keeping the Filename as a Key field in the Transpose tool (along with all other fields except the one you want to rename), and then using that Filename as your New Column Header in the Cross-Tab tool (with Value field as Value for New Columns and all other fields Except the Name, Value, and Filename as your Group Data fields), then you should get the filename to show up as the column name for each spend category.

I included two "Inputs" with different Filenames to show how it might group things based on filename, which I'm guessing might be one of the purposes of your workflow... hope that helps!

Cheers,

NJ

Claje · Accepted Answer

Just wanted to add a suggestion for some scalability as well, as transpose+cross tab can be slow on very large datasets.

Effectively, by using Summary and Field Info, you can apply this logic to only a small portion of your data, which should run faster.  This probably doesn't matter if the file is <100MB, or only has a few columns, but it could save a few seconds of runtime if you're working with larger files than that.