Data preparation

Question

Hi  Alteryx experts,

Please help me to transform this data set:

YearQTRvalue2016Q2Fcst02016Q3Fcst02016Q4Fcst02016Q1Act02016Q2Act02016Q3Act02016Q4Act13181.822015Q1Act02015Q2Act227042015Q3Act65416.52015Q4Act02014Q1Act02014Q2Act7370.992014Q3Act109831.52014Q4Act-8707.52017Q1Plan02017Q2Plan02017Q3Plan02017Q4Plan02016Q1Plan02016Q2Plan02016Q3Plan02016Q4Plan02016Q1Fcst02016Q2Fcst02016Q3Fcst02016Q4Fcst02016Q1Act02016Q2Act02016Q3Act02016Q4Act02015Q1Act02015Q2Act02015Q3Act02015Q4Act02014Q1Act02014Q2Act02014Q3Act0

I want the final result like this:

year qtrActPlanFsct2016q2  02016q3  02016q4  02016q10  2016q20  2016q30  2016Q413181.82  2015Q10  2015Q222704  2015Q365416.5  2015Q40  2014Q10  2014Q27370.99  2014Q3109831.5  2014Q4-8707.5  2017Q1 0 2017Q2 0 2017Q3 0 2017Q4 0 2016Q1 0 2016Q2 0 2016Q3 0 2016Q4 0 2016Q1  02016Q2  02016Q3  02016Q3  0

Please help me with this request.

Thanks,

Tharashasank

DataBlender · Accepted Answer

Hi @Tharashasank

Did you specifically want there to be only one value from Act, Fcst or Plan per row?

The attached workflow has fewer rows as some of the original rows have been combined.

The approach was to split the Qtr field into the Quarter and then type of finance info. Then the cross tab tool is used to get this into the three separate columns

DataBlender · Accepted Answer

That's correct, but since you only have one value per quarter/type combination it gets to the same result. You could of course use a different aggregation method, so long as one is used.