merged header to column

[Haokun]

Hi I have a data set likes below, the first row has some period header which I want to make those period into one of the column.

				1month			3months
Fund Name	FUM	Inception Date	FMC	Fund	BM	REL	Fund	BM	REL
CITI	100	1/11/1990	1	1.5	6	0.5	2.25	9	0.75
SCB	200	1/11/1990	2	2	4	1	3	6	1.5
JP	300	1/11/1990	1	-1	-4	-0.5	-1.5	-6	-0.75
ALLIANCE	400	1/11/1990	2	-2	-5	-1	-3	-7.5	-1.5

May I know how to transform to the below table?

Fund Name	FUM	Inception Date	FMC	Period	Fund	BM	REL
CITI	100	1/11/1990	1	1month	1.5	6	0.5
SCB	200	1/11/1990	2	1month	2	4	1
JP	300	1/11/1990	1	1month	-1	-4	-0.5
ALLIANCE	400	1/11/1990	2	1month	-2	-5	-1
CITI	100	1/11/1990	1	3months	2.25	9	0.75
SCB	200	1/11/1990	2	3months	3	6	1.5
JP	300	1/11/1990	1	3months	-1.5	-6	-0.75
ALLIANCE	400	1/11/1990	2	3months	-3	-7.5	-1.5

Thanks guys.

[Qiu]

@Haokun 
Hope this is what you need.

[anjaliaithal]

Please check the attached solution.

[Haokun]

hi @Qiu 

Thanks! but there will be alot of periods behind, such as 9months, 1 year, year to date, since inception and so on.

Is there an easier way without using Regex?

[atcodedog05]

Hi @Haokun 

Is it possible to share an excel file of the sample data. So that the solution can be tailored according to data structure.

[AngelosPachis]

Hi @Haokun ,

If you group in the first 4 fields (Fund Name, FUM, Inception Date and FMC) before transposing and assign some record ID to maintain the sorting order of the original file, then with a join you can make a workflow quite robust and it will work for more periods than just those provided.

Hope that helps, let me know if there are any questions on the workflow.

Regards,

Angelos

[Haokun]

hi @AngelosPachis 

are you able to save your workflow to yxmd format? i am not sure why i can't open your workflow but seems your workflow is what i want. 

[AngelosPachis · Accepted solution]

Oh I see, probably that's my bad, this .yxzp file is a packaged workflow and it is not supported in all Alteryx versions.

I've attached the workflow in a .yxmd and the input file it uses; I've created an .xlsx file which would look similar to your input,

Let me know if it worked for you this time.