Hi Alteryx experts,
Please help me to transform this data set:
Year | QTR | value |
2016 | Q2Fcst | 0 |
2016 | Q3Fcst | 0 |
2016 | Q4Fcst | 0 |
2016 | Q1Act | 0 |
2016 | Q2Act | 0 |
2016 | Q3Act | 0 |
2016 | Q4Act | 13181.82 |
2015 | Q1Act | 0 |
2015 | Q2Act | 22704 |
2015 | Q3Act | 65416.5 |
2015 | Q4Act | 0 |
2014 | Q1Act | 0 |
2014 | Q2Act | 7370.99 |
2014 | Q3Act | 109831.5 |
2014 | Q4Act | -8707.5 |
2017 | Q1Plan | 0 |
2017 | Q2Plan | 0 |
2017 | Q3Plan | 0 |
2017 | Q4Plan | 0 |
2016 | Q1Plan | 0 |
2016 | Q2Plan | 0 |
2016 | Q3Plan | 0 |
2016 | Q4Plan | 0 |
2016 | Q1Fcst | 0 |
2016 | Q2Fcst | 0 |
2016 | Q3Fcst | 0 |
2016 | Q4Fcst | 0 |
2016 | Q1Act | 0 |
2016 | Q2Act | 0 |
2016 | Q3Act | 0 |
2016 | Q4Act | 0 |
2015 | Q1Act | 0 |
2015 | Q2Act | 0 |
2015 | Q3Act | 0 |
2015 | Q4Act | 0 |
2014 | Q1Act | 0 |
2014 | Q2Act | 0 |
2014 | Q3Act | 0 |
I want the final result like this:
year | qtr | Act | Plan | Fsct |
2016 | q2 | 0 | ||
2016 | q3 | 0 | ||
2016 | q4 | 0 | ||
2016 | q1 | 0 | ||
2016 | q2 | 0 | ||
2016 | q3 | 0 | ||
2016 | Q4 | 13181.82 | ||
2015 | Q1 | 0 | ||
2015 | Q2 | 22704 | ||
2015 | Q3 | 65416.5 | ||
2015 | Q4 | 0 | ||
2014 | Q1 | 0 | ||
2014 | Q2 | 7370.99 | ||
2014 | Q3 | 109831.5 | ||
2014 | Q4 | -8707.5 | ||
2017 | Q1 | 0 | ||
2017 | Q2 | 0 | ||
2017 | Q3 | 0 | ||
2017 | Q4 | 0 | ||
2016 | Q1 | 0 | ||
2016 | Q2 | 0 | ||
2016 | Q3 | 0 | ||
2016 | Q4 | 0 | ||
2016 | Q1 | 0 | ||
2016 | Q2 | 0 | ||
2016 | Q3 | 0 | ||
2016 | Q3 | 0 |
Please help me with this request.
Thanks,
Tharashasank
Solved! Go to Solution.
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
Did you specifically want there to be only one value from Act, Fcst or Plan per row?
Yes
https://community.alteryx.com/t5/user/viewprofilepage/user-id/5127
Did you specifically want there to be only one value from Act, Fcst or Plan per row?
yes
In your present solution it is doing sum.
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.