Create a matrix from rated results on products based on critearias

[nina_]

Hi everyone, 

I am trying to create a matrix in the following format (Products x Criteria):

Matrix	C1	C2	C3	C4	C5
P1	5	8	9	2	9
P2	3	0	2	…	…
…	…	…	…	…	…

The data that I have is all in various columns (about 2'000), in this format

P1_C1	P1_C2	P1_C3	P1_C4	P1_C5	P2_C1	P3_C2	_P4_C3
5	8	9	2	9	3	0	2

In Excel I would use a Pivot Table on the original data, and separate it into the matrix using the getpivotdata formula. 

Do you have any ideas how to do this matrix in Alteryx? 

Thanks for any tipps! 

[BenMoss · Accepted solution]

There's a relatively straight forward solution here.

Use the transpose tool to pivot your data into a single column.

Use the text to columns tool to split your 'name' field into two columns. Name1 will contain p1 and name2 will contain c1.

You can then use the cross tab tool to bring your data into the desired format.

Your group by field would be the column that contains your row headed, so Name1.

Name2 will be your new column headers.

And the value field contains your values.

Give this a try.

Ben

[nina_]

Thanks so much. That worked perfectly!