Designer| Data Transformation

Question

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

CodeFromDateToDateReturnMVEAM-CACO#AAAGG5/31/20216/30/202122%0.314398AM-CACO#AAAGG4/30/20215/31/202156%0.845102AM-CACO#AAAGG3/31/20214/30/202141%0.765227AM-CACO#AABAL5/31/20216/30/202147%0.029487AM-CACO#AABAL4/30/20215/31/202113%0.208862AM-CACO#AABAL3/31/20214/30/202122%0.990837AM-CACO#AACON5/31/20216/30/202117%0.732135AM-CACO#AACON4/30/20215/31/202122%0.762674AM-CACO#AACON3/31/20214/30/202173%0.370564

Output

CodeJun-21May-21Apr-21MVEAM-CACO#AAAGG22%56%41%0.314398AM-CACO#AABAL47%13%22%0.029487AM-CACO#AACON17%22%73%0.732135

test.xlsx

AngelosPachis · Accepted Answer

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