Rename the header

[Haokun]

Hi,

i have two column headers

2021_010_Acr and 2021_010_Act, which I would like to change to Oct21 Acr and Oct21 Act by using dynamic rename tool. Please noted that the header name will be changed based on different period, so a simple select tool will not solve the problem.

I tried to use the below formula in the dynamic rename tool,

if REGEX_Match([_CurrentField_], "\d{4}\_\d{3}.*")
then DATETIMEFORMAT(DATETIMEPARSE(REGEX_Replace([_CurrentField_], "\_0", ""),"%Y%m"),"%b-%y*")
else [_CurrentField_]
endif

but it produces the incorrect header names. 

anyone has any answer? thanks too much.

[Jonathan-Sherman · Accepted solution]

Hi @Haokun,

I'm not sure whether you wanted to keep the _Acr / _Act on the end of the header, if so you could use:

IF REGEX_Match([_CurrentField_], '^\d{4}_\d{3}.*')
THEN DateTimeFormat(DateTimeParse([_CurrentField_],'%Y_0%m'),'%b-%y') + REGEX_Replace([_CurrentField_], '^\d{4}_\d{3}(.*)', '$1')
ELSE [_CurrentField_]
ENDIF

If not you could use:

IF REGEX_Match([_CurrentField_], '^\d{4}_\d{3}.*')
THEN DateTimeFormat(DateTimeParse([_CurrentField_],'%Y_0%m'),'%b-%y')
ELSE [_CurrentField_]
ENDIF

If you've got any questions just let me know!

Kind regards,

Jonathan

[binuacs]

@Haokun another option

[Haokun]

@Jonathan-Sherman 

Hi Jonathan,

thanks so much! it works!!! 

@binuacs also thanks to your reply, i think the regex Jonathan provided is easier for me to explain to the users, however yours also works perfectly.

thanks all.