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!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
atcodedog05
22 - Nova
22 - Nova

by Kiran Manjunatha and Bhumika Agarwal (@BA02)


Once upon a time, before you read this awesome article:

 

Stakeholder: “Hey! Can you merge those cells like we do in our Excel workbooks? Also, providing a common header would be a good idea too.”

 

Alteryx developer: “Naah, I don’t think so. Not possible using Alteryx.”

 

atcodedog05_0-1658863257950.png

 

Source: GIPHY 

 

What if we say, “Yes, it is possible! And here is how.” (Given you are a “jugaad” developer.)

 

With the intention of not making this guide very long and boring at the beginning itself, here’s some good news! It will be a three part series. Woohooo! Here is the outline of the series:

 

1. How to Create an Alteryx Report with Merged Rows (you are here)

 

atcodedog05_2-1658863258261.png

 

2. How to Create an Alteryx Report with Merged Columns

 

MeganDibble_0-1658871782353.png

 

3. How to Create an Alteryx Report with Both Merged Rows and Merged Columns

 

MeganDibble_1-1658871806330.png

 

Due to our lack of capability to generate cool datasets, we shall be using a subset of publicly available superstore sample data.

 

Let’s get to it!

 

How to Create an Alteryx Report with Merged Row Cells

 

Prepare the data in the desired format with all the required columns you wish to see in your output. Refer to the example below.

 

MeganDibble_2-1658871861551.png

 

Now let's start by converting the data into the desired report first.

 

The concept here is that your merged cells row will become the parent row, and all the other rows in other columns will become the rows in the child table.

 

Step 1: Create child tables for each parent merge row cell

 

Insert a Basic Table tool and configure it like below:

 

MeganDibble_0-1658873733600.png

 

 

Additionally, configure the tool to add the borders, background column, alignment, etc. [Optional]

 

Step 2: Create the parent table with nested child tables

 

Insert a Basic Table tool and configure it like below:

 

MeganDibble_1-1658874045204.png

 

Additionally, configure the tool to add the borders, background column, alignment, etc. [Optional]

 

This specific step (step 2) will create row merged cells.

 

Step 3: Now, let’s add the column headings!

 

In a Text Input tool, input headings as a row like below:

 

MeganDibble_2-1658874207002.png

 

Insert a Basic Table tool and configure it as:

 

MeganDibble_3-1658874243053.png

 

Now use the union tool to add the headings on top of the data tables. Make sure to configure the output order accordingly.

 

MeganDibble_4-1658874262393.png

 

Step 4 [Excel]: Phew! Output

 

Add a Render tool and configure it to write it to Excel.

 

MeganDibble_5-1658874315706.png

 

Excel report output:

 

MeganDibble_6-1658874345212.png

 

Other Formats

 

“We don’t want it in Excel. Any other formats which Alteryx can render to?”

 

For output formats other than Excel, we need to add an extra step to get the borders to align. Add a Layout tool and configure it as below:

 

MeganDibble_7-1658874416274.png

 

After the Layout tool, add a Render tool and configure it to the required file format. This table format after the Layout tool can also be used in the body section of an Email tool!

 

Optional Step [Other Formats]

 

You might have observed that in formats other than Excel, you can see the extra line like the one below the Technology row.

 

MeganDibble_8-1658874453089.png

 

You can make the extra lines less visible by using white borders and light grey background for the table cells like below. This will give a more elegant look. Nice!

 

MeganDibble_9-1658874459476.png

 

We hope this was helpful. Feel free to reply to this post and hit us up with your queries if you are facing any issues or need any clarifications. We shall be happy to help.

 

Cheers and Happy Analyzing :-)

 

Source: GIPHY

 

Stay tuned for the next part of this blog! We hope you’re as excited as we are for this blog series!

 

Read Part 2 Here.

 

Comments
collin_pace
9 - Comet

I was excited when I saw this blog series, thinking there was finally a new tool and/or setting in an existing tool to configure these types of table output options.  The step-by-step instructions here are really helpful, but I would love Alteryx to develop a much easier way to output data in these types of merge cell formats with settings inside a single table tool.  Something like an additional section in the table tool to, select the fields you'd like to [merge rows/merge columns] and number of rows/columns to merge [ numeric value ]. I'm sure it's way more complicated than that, but just a feature request. 

 

MeganDibble
Alteryx Community Team
Alteryx Community Team

@collin_pace You can give a like to this idea that someone submitted for Designer to try to resurface it :)  https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Multiple-row-headers/idi-p/29317

 

collin_pace
9 - Comet

Thanks @MeganDibble for the link.  Funny enough, after clicking it, I found that I already liked this idea at some point in the past.  Happy to see you bring more attention to it!  Maybe it gains some traction.

archelaus
5 - Atom

this is very helpful. thank you especially for the easy-to-follow steps.

AsmiDesai
9 - Comet

This is some cool + easy stuff !👍 Thank you @atcodedog05  and @BA02  ...

Deepali77
5 - Atom

I was able to follow it and do it on my own! Woohooo!! Thanks @atcodedog05  & @BA02 

Joyce-Swapna
5 - Atom

This is great stuff. :) 

atcodedog05
22 - Nova
22 - Nova

Thank you 🙂 @archelaus @AsmiDesai @Deepali77 @Joyce-Swapna 

 

Happy to hear that you folks found it helpful and easy to follow 😀

 

 

binuacs
20 - Arcturus

Nice work @atcodedog05 !!!!!

DawnDuong
13 - Pulsar
13 - Pulsar

Hello @atcodedog05  I don't know why I only see this series today. Very cool and thank you!

This is SO helpful. Thank you very much!!

thulasir
6 - Meteoroid

Need of time, Thank you

AMCM0806
6 - Meteoroid

wow this is very helpful. The only issue I run into after rendering to excel, is when I filter on a merged column, it will only pull up one row. In the sample dataset, in excel if I filter on Segment to Consumer, only one row of data will appear. Is there a way to get the other categories aligned to Consumer (Office Supplies and Technology) to appear?

 

AMCM0806_0-1683837481036.png

 

sparksun
11 - Bolide

Never too later to see such a terrific post!

SeanAdams
17 - Castor
17 - Castor

This is a very useful post & series Kiran & Bhumika ( @atcodedog05 @BA02 ) - thank you for this.

As you say - it's a very common request that we see frequently from our users - and this is a simple way to achieve it.