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

Thanks @MeganBowers 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.

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.