Transpose Data

[goutdelete]

Hi everyone!

I have some data like this in sheet1, essentially I need to re-arrange the data into something like this (in sheet2):

Asset Class	2021 Q3	2021 Q4	2022 Q1	2022 Q2	2021 Q3	2021 Q4	2022 Q1	2022 Q2	2021 Q3	2021 Q4	2022 Q1	2022 Q2
	PD_S1				PD_S2				PD_S3
1	0.000247	0.000286	0.000139	0.000114	0.00026	0.0003	0.000143	0.000132
2	0.004046	0.004538	0.002557	0.002181	0.00421	0.004718	0.002619	0.002461
3	0.028058	0.030668	0.019618	0.017317	0.028937	0.031604	0.019992	0.019039
4	0.034633	0.037734	0.02452	0.021735	0.035678	0.038842	0.024971	0.023819
5	0.035657	0.038832	0.025289	0.022431	0.036727	0.039967	0.025752	0.02457
6	0.046887	0.050842	0.033821	0.030171	0.048222	0.05225	0.03441	0.032905
7	0.075049	0.080739	0.055833	0.05033	0.076976	0.082754	0.056714	0.054459
8	0.120116	0.128118	0.092425	0.084275	0.122835	0.13093	0.09372	0.0904
9	0.196365	0.207362	0.157149	0.145221	0.200117	0.211196	0.159028	0.154203
10	0.196365	0.207362	0.157149	0.145221	0.200117	0.211196	0.159028	0.154203

I feel transpose may be the answer but so far I haven't had any luck.  The result is just not what I wanted.  I suppose it's not a traditional "transpose" either, basically every 10 records is a set of assets (let's just call it 1 to 10), and the value changes each period under each environment scenatio.  I would like to get the assets 1-10 as row and all the values append in columns.

Anyone might be able to help me out please? 🙏

Thanks a million!

[AngelosPachis · Accepted solution]

Hi @goutdelete 

I can easily spot one issue that you are going to have if you get the data in the format shown in your post above and that is that the column headers are the same. In AlteAryx this is not possible. As an alternative, in the attached workflow I have included each environment scenario in the column headers

Hope that helps,

Angelos

[goutdelete]

@AngelosPachis 

Thanks so much!!  We were rather close but when we tried the crosstab tool somehow neither my coworker nor I got the correct result.  I suppose we need to study cross tab tool more 😂