Designer|Transpose|Cross-Tab| Data Transformation

[Mj9715]

Hi All, 

I have a input data set similar to below - where each ISIN code has two rows (fund and benchmark) and associated returns for 3m, 1Y,3Y and 5Y. 

For the output I would like to use the ISIN code in output table as the join field and transpose each field's first two rows into columns. For example, for the first ISIN code the first two rows in 3m needs to be transposed to columns with fund and benchmark as the headers. I have pasted the output format below with an example. 

Both input and desired output is in file attached.. Please can someone guide on the best way to achieve this using cross-tab and join tools?

Input 

Identifier	Fund	3m	1Y	3Y	5Y	ISIN
Fund	HSBC FTSE 100 UCITS ETF	0.94	0.52	0.33	0.85	IE00B42TW061
Benchmark	FTSE 100	0.58	0.80	0.37	0.72	IE00B42TW061
Fund	HSBC Euro Stoxx 50 UCITS ETF	0.13	0.37	0.48	0.60	IE00B4K6B022
Benchmark	DJ Euro Stoxx 50	0.39	0.46	0.19	0.88	IE00B4K6B022
Fund	HSBC CAC 40 UCITS ETF	0.07	0.46	0.65	0.23	IE00B4L49M32
Benchmark	CAC 40	0.24	0.67	0.18	0.40	IE00B4L49M32

Output 

ISIN	Fund Return 3M	Benchmark Return (3M)	Fund Return 1Y	Benchmark Return (1Y)	Fund Return 3Y	Benchmark Return (3Y)	Fund Return 5Y	Benchmark Return (5Y)
IE00B42TW061	0.94	0.58	0.52	0.80	0.33	0.37	0.85	0.72

[atcodedog05 · Accepted solution]

Hi @Mj9715 

Here is how you can do it.

Workflow:

Hope this helps : )

[Kamran1991 · Accepted solution]

Hi @Mj9715 ,

I hope this workflow can solve you problem

[Mj9715 · Accepted solution]

Thank you @atcodedog05 

[atcodedog05]

Happy to help : ) @Mj9715 

Cheers and have a nice day!

[Mj9715]

Thanks @Kamran1991 - this also works as a solution. 🙂