Dynamic Column Header Name change

[ayadav8]

Hey,

I got two reports, one from current month (denoted as "CM") second is from Previous month ("PM"). I use join tool to merge these files. I want the header names to be changed dynamically based on the month I am running the report.

For example: If I am running the report in Nov'17

CM to changed as Nov'17

PM to be changed as Oct'17

Attached excel file will clearly show the requirement

I know there is a tool "Dynamic Rename" but I am not sure if it can give me desire result. 

Any help is appreciated. Thank you!!

[Claje]

If you use the Dynamic Rename tool in the mode "Formula" and select all fields in the tool, the following formula should do what you need.

IF [_CurrentField_] = 'PM' THEN DATETIMEFORMAT(DATETIMEADD(DATETIMETODAY(),-1,'months'),"%b'%y") ELSEIF [_CurrentField_] = 'CM' THEN DATETIMEFORMAT(DATETIMETODAY(),"%b'%y") ELSE [_CurrentField_] ENDIF

This will check the field name of each field, and if it is PM it will display last month's name, if it is CM it will display this month, and otherwise it will keep the same name.

[ayadav8]

@Claje Thanks for replying, I added "Contains" in the formula and it worked but the output is not the one I wanted. Output here is Nov'17, Nov'17_2, Nov'17_3 but I need Nov'17 Scenario, Nov'17 Quantity and Nov'17 Cost. I need string addition in the dates to segregate them. 

[Claje · Accepted solution]

If you change the prior formula to the following it should meet that need:

IF CONTAINS([_CurrentField_],'PM') THEN REPLACE([_CurrentField_],'PM',DATETIMEFORMAT(DATETIMEADD(DATETIMETODAY(),-1,'months'),"%b'%y")) ELSEIF CONTAINS([_CurrentField_],'CM') THEN REPLACE([_CurrentField_],'CM',DATETIMEFORMAT(DATETIMETODAY(),"%b'%y")) ELSE [_CurrentField_] ENDIF

Basically, this way we will only replace the 'PM' or 'CM' values in your field names, rather than replacing the entire thing.

[ayadav8]

This is exactly what I wanted. Thanks!!