How can I create a pivot table with two layers?

[alteryxisconfusing]

I want to use Alteryx to take some raw data and output a pivot table in Excel that shows each line item (student) with two sub line items (Test 1 and Test 2) and their associated score, is this possible?

For example:

Student	Section	Score
Ashley	Test 1	100
	Test 2	75
Julie	Test 1	85
	Test 2	90
John	Test 1	66
	Test 2	70
Natalie	Test 1	95

[binuacs]

@alteryxisconfusing Can you provide the sample input file?

[alteryxisconfusing]

The input (raw data) is just another Excel workbook that looks like this:

Name	Test	Score
Ashley	Test 1	100
Ashley	Test 2	75
Julie	Test 1	85
Julie	Test 2	90
John	Test 1	66
John	Test 2	70
Natalie	Test 1	95

Basically, I'm trying to see the test and scores grouped by student name in the pivot table.

[Luke_C]

Hi @alteryxisconfusing 

You could use a multi-row tool to get the presentation you desire, but this isn't a true pivot table. Pivot functionality is usually done by the summarize/transpose/crosstab tools.

[alteryxisconfusing]

Is there a way to tell Alteryx to put it into Excel as a pivot table? That way it has the layout you presented, but I could also use the pivot tool in Excel to see different views? 

If not, I suppose I can just use the output you suggested and then manually add the pivot feature once I am in Excel (kind of defeats the purpose of Alteryx though).

[Luke_C]

Hi @alteryxisconfusing 

You could use an excel template - output the data to a table in the excel file that is the source of the pivot table. Although the better question would be what analysis/reporting are you doing with the pivot? That's what you would replace with Alteryx.

[alteryxisconfusing]

It's mainly filters we would be using like specific students and maybe averages for them, or metrics on groups of students and their scores, etc.

Are you saying the flow would be:

Raw data input > Alteryx formats the table (like what you showed with the name, skip a row, expression) >  Alteryx outputs the table to an Excel file

If Alteryx can't tell Excel to add the pivot table feature to the table, can it tell Excel to autosize the column widths, add a standard filter for the table, add a border, and add color formatting? Is that possible?

[grazitti_sapna]

@alteryxisconfusing , It is possible to add formatting to excel file. In that case you will have to apply formatting to excel before loading your data and then check preserve format on overwriting option and mention the range with file in which you want to preserve the format you have applied. In this way your data will be written to the formatted excel.

Other workaround is you can create a table of the output and format it the way you want and then save it in an excel file. I have given examples for both. 

Please accept if it was useful.

[alteryxisconfusing]

Thanks for adding those examples.

Couple questions:

1. How did you set the colors in the table and render tool?

2. How can I have Alteryx tell Excel to add a standard filter for the table and add a border? Is that possible?