Renaming column headers

[Sumit_Das]

Hello All, would be much appreciated if you could help on the below section:

I have a raw data, whose column name looks like this:

T-3	T-2	T-1	T	T+1	T+2	T+3	T+4	T+5	T+6	T+7	T+8
1	1	1	1	1	1	1	1	1	1	1	1

And I want to update the column name to below output

2000-Q1	2000-Q2	2000-Q3	2000-Q4	2001-Q1	2001-Q2	2001-Q3	2001-Q4	2002-Q1	2002-Q2	2002-Q3	2002-Q4
1	1	1	1	1	1	1	1	1	1	1	1

Please note that, my dataset consist of more than 100 columns in continuation of that one. (so manual intervention using tool Select tool will not be helpful for me)

Thanks in advance!

[Sumit_Das]

Thanks marlontalisvista,

But I have just shared a sample data aforesaid, My dataset consist of more than 100 columns in continuation of that one..So, I believe manually wouldn't be much helpful.If you have any other solution (like using Transpose, Multi-Row and Dynamic Rename or any other tools)  please advise.

[PhilipMannering · Accepted solution]

Hi @Sumit_Das 

This isn't trivial, but see if the attached solves for your need. You need to set T, then everything else is relative to that one Quarter. Let me know if any issues/questions.

Thanks,

Philip

[danilang · Accepted solution]

Hi @Sumit_Das 

Here's a different take.  

It uses the Field info tool to extract the column names.  The T Year and Quarter Text Input is where you specify the year and quarter that match your T.  After finding the T column name in the Join tool and appending match info, all the work takes place in the Formula tool.   

First the offset from the T year is extracted from the column name.  Then the Q is calculated for each row and then the corresponding year.  Finally these are combined into the NewName column.  This is used in the Dynamic Rename to rename the columns.

Since the workflow acts on the metadata and doesn't have to sort the data, its run time is independent on the number of rows in the data set.

Dan 

[Sumit_Das]

Thanks Philip and Dan, that's really helpful for me. Cheers

[sgwong]

Seek your advice on dymanic rename header on date format only.

Input Data:

Output Data: