Data preparation

[Tharashasank]

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

[DataBlender · Accepted solution]

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

[Tharashasank]

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

  Yes

[Tharashasank]

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.

[DataBlender · Accepted solution]

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.