Designer| Data Transformation

[Mj9715]

Hi All, 

I have a dataset which I have latest three month data per each code group. (Input attached). I would like to create an output that transposes the return value of the three months and dates as the column name and row identifier as the code group. as for the MVE is always needs to pick up the first row value per code group. Output also attached in same sheet. Please can someone suggest the best way to achieve this?

Input

Code	FromDate	ToDate	Return	MVE
AM-CACO#AAAGG	5/31/2021	6/30/2021	22%	0.314398
AM-CACO#AAAGG	4/30/2021	5/31/2021	56%	0.845102
AM-CACO#AAAGG	3/31/2021	4/30/2021	41%	0.765227
AM-CACO#AABAL	5/31/2021	6/30/2021	47%	0.029487
AM-CACO#AABAL	4/30/2021	5/31/2021	13%	0.208862
AM-CACO#AABAL	3/31/2021	4/30/2021	22%	0.990837
AM-CACO#AACON	5/31/2021	6/30/2021	17%	0.732135
AM-CACO#AACON	4/30/2021	5/31/2021	22%	0.762674
AM-CACO#AACON	3/31/2021	4/30/2021	73%	0.370564

Output

Code	Jun-21	May-21	Apr-21	MVE
AM-CACO#AAAGG	22%	56%	41%	0.314398
AM-CACO#AABAL	47%	13%	22%	0.029487
AM-CACO#AACON	17%	22%	73%	0.732135

[AngelosPachis · Accepted solution]

Hi @Mj9715 ,

I think what you should do is first parse the dates into a new column, which you can then use as your column headers when you cross tab your data. Then you can easily keep the first record per group by using a sample tool and finally bring those two streams together,

Hope that helps,

Angelos