Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Creating pivot table like Excel

Jimmylee4real
8 - Asteroid

hello all,

 

I haven trying to create pivot table in alteryx. Can anyone guide me to do so? I have attached the excel sheet with the ideal output I want. I want to output the same table by using Alteryx

 

with data: 

demo01.PNG

 
 

Ideal pivot table output: 

demo02.PNG

 

Thanks

10 REPLIES 10
ArnavS
Alteryx
Alteryx

Hi Jimmylee4real,

 

Have you tried using the Summarize to:

1. Group by: Software, Type, Person, Purchase Date

2. Sum Quantity Count

 

Let me know if that gets you to your desired result or if you could upload some sample data to better assist. 

 

Cheers,

Arnav

Jimmylee4real
8 - Asteroid

hello, thanks for your help but it doesn't output the format i want. 

fyi 

Jimmylee4real_0-1595490216766.png

I actually want to output a pivot table with specific format above, which clearly shows the software, the person from which particular company with quantity count. Is there any tool I could use in Alteryx to do this? thanks in advance

 

ponraj
13 - Pulsar

@Jimmylee4real can you share sample data and expected output in excel format ? 

Jimmylee4real
8 - Asteroid

hello sir, 

 

please refer to below screen captures for your reference

 

thanks!

 

Sample Data: 

Jimmylee4real_0-1596010488924.png

 

Expected Output in Pivot Table format 

Jimmylee4real_1-1596010526587.png

1. There should be "Pre-headers" on top to show Software and Type. Could we use "Report Text" to do it?

2. For A3 type, it should shows the quantity count 

3. For other types, it should tells us the "type" in the middle (A1, A2 & A3 highlighted in red) 

 

What must be included: 

1. Person's name 

2. his/her Company 

3. Purchase Date 

CharlieS
17 - Castor
17 - Castor

This can be done, but I recommend a few changes.

- These uneven columns (headers to data fields) can be made to work, but it's very manual and don't scale well to other situations. 

- I highly recommend one line of data per transaction. The multi line of company + name gets messy to report. In my attached example, I use separate name and company fields, 

 

20200731-Pivot.PNG

 

Note about the headers::

- To build headers that fit as we want, we need to build them as tables with the same number of fields and rows. You'll see I have the Text Input tools with those empty fields that I used for the header tables. This allows everything to line up, but done some screwy things with fields outside the rendered areas (check out cell B30 in the example output)

 

Check out the attached workflow and let me know if you have any questions. 

Jimmylee4real
8 - Asteroid

thank you so much for taking your time for providing this wonderful solution. Do you think is there any better alternative to show the result in a more comprehensive format so whoever reading this report have a better understanding? 

 

thanks 

CharlieS
17 - Castor
17 - Castor

I would prefer something more simple like this:

 

20200803-Pivot2.PNG

 

- Consolidated the columns to a single set. Since the fields are duplicated by type and only one type is ever assigned to each, then 2 sets of fields are always empty. That's a lot of unused space, so I transformed it and added a field of category type data so it can be easily filtered if the user wants. 

- Removed duplicated headers 

- This consolidates the data, makes it easier to filter/sort if the user wants

Jimmylee4real
8 - Asteroid

thanks for your help, i really appreciate it! just two more questions and i will close this issue

 

1. is it possible to add an image on the top right hand corner? ex. company logo

- i try to use the "Report Text" tool to do this but the image is a little bit too large 

2. is it possible to rename the sheet tab on the final output file while keeping the contents on top of the data?

- i try to use the layout tool with orientation - Vertical Merge Content to Line Up Table Columns. Although it shows the layout format I want (contents + data) but it doesn't allow me to rename the sheet tabs. 

 

"Vertical with section breaks" works fine but it completely separate the content from data.

 

thanks!

CharlieS
17 - Castor
17 - Castor
@Jimmylee4real wrote:

1. is it possible to add an image on the top right hand corner? ex. company logo

- i try to use the "Report Text" tool to do this but the image is a little bit too large 


Absolutely. The Image tool will provide you with better options on managing an image file. In the attached example, I add a header that features some title info on the left and an image on the right. 

 


@Jimmylee4real wrote:

2. is it possible to rename the sheet tab on the final output file while keeping the contents on top of the data?

- i try to use the layout tool with orientation - Vertical Merge Content to Line Up Table Columns. Although it shows the layout format I want (contents + data) but it doesn't allow me to rename the sheet


The best way to get custom named sheets in Rendered Excel output is to prepare a layout field for each sheet first with a sheet name field, then in a Layout tool, group by the sheet name, arrange them "Vertical with Section Breaks" (section breaks = sheet breaks in Excel), and use the sheet name as the "Section Name" setting at the bottom. In the attached example, this is done by Layout (90).

 

20200804-Pivot3.PNG

Labels