Data Transformation - Crosstab / Transpose

[vinakota]

Dear Alteryx Community

I am a newbie to the tool and needed some help. I have a specified input data format that I am looking to transform to a specified output data format. I have attached the sample file here for your reference. I appreciate any examples/suggestions that the community may be able to share. 

Input

Time Period	Actual Demand	Your Forecast	APE	MAPE (%)	Paricipant ID	File Name
T1	505				12345678	ABC
T2	493				12345678	ABC
T3	521				12345678	ABC
T4	504				12345678	ABC
T5	547				12345678	ABC
T6	501	540	0.077844311	0.077844311	12345678	ABC
T7	531	499	0.060263653	0.069053982	12345678	ABC
T8	543	520	0.042357274	0.06015508	12345678	ABC
T9	540	525	0.027777778	0.052060754	12345678	ABC
T10	549	530	0.034608379	0.048570279	12345678	ABC
T1	542				12345679	ABD
T2	482				12345679	ABD
T3	482				12345679	ABD
T4	533				12345679	ABD
T5	488				12345679	ABD
T6	492	540	0.097560976	0.097560976	12345679	ABD
T7	480	500	0.041666667	0.069613821	12345679	ABD
T8	493	490	0.006085193	0.048437612	12345679	ABD
T9	522	480	0.08045977	0.056443151	12345679	ABD
T10	523	500	0.043977055	0.053949932	12345679	ABD

Output

Paricipant ID	File_Name	T6_Actual Demand	T7_Actual Demand	T8_Actual Demand	T9_Actual Demand	T10_Actual Demand	T6_Your Forecast	T7_Your Forecast	T8_Your Forecast	T9_Your Forecast	T10_Your Forecast
12345678	ABC	501	531	543	540	549	540	499	520	525	530
12345679	ABD
12345680	ABE

[SPetrie]

You just need to cleanup the data and do a bit of prep for the final column headers. 

I removed the extra columns since it doesnt look like they are used in your example output. I also got rid of any rows that didnt have a Forecast since it looks like those are not in the final output either.

I do a transpose to get them lined up vertically and then use a formula to combine the time period with the original column name to get the new output headers. I also added the first letter of the name at the beginning of the new name to get a better sorting after the crosstab.

After crosstab, I use a dynamic rename to remove the extra bit I put on front to get the final output.

This is what the sorting would look like after cross tab if I didnt add the extra bit to the new name first.

You could rearrange them with a select tool later, but the method I used forces a better alphabetical sorting so you dont have to worry about it later on if fields get added/changed.

Im sure there is an easier way of doing it but Im too lazy to look it up at the moment :)

[vinakota]

Very helpful and exactly what I needed. Thank you... 

Just some advise.. If I have to create calculation based on forecast and the demand for each period,do you suggest creating them first in excel and then transposing in Alteryx or can I perform them in alteryx directly? 

Regards,

Srikant.

[SPetrie]

Its up to you. You can do them in excel first or directly in Alteryx with the formula tool. If you do it in Alteryx, put it in before the transpose and it should bring in the new field for the output.