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 create merge cells column headers like we do in Excel? That would be super helpful.”

 

AlteryxMatt_0-1659563192836.png

 

Alteryx developer: I know how to merge row cells from Part 1. But merging columns is still a mystery to me.

 

After reading this article:

 

Alteryx developer: “Yes, it is possible! And I know how.”

 

Source: GIPHY

 

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. Woohoo! Here is the outline of the series:

 

1. How to Create an Alteryx Report with Merged Rows

 

AlteryxMatt_2-1659563194535.png

 

2. How to Create an Alteryx Report with Merged Columns (you are here)

 

AlteryxMatt_3-1659563194571.png

 

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

 

AlteryxMatt_4-1659563194662.png

 

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

 

In this post, we will be covering part 2. If you missed part 1, click here to go to that blog.

 

Let’s get to it!

 

How to Create an Alteryx Report with Merged Columns

 

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

 

AlteryxMatt_5-1659563194723.png

 

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

 

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

 

Step 1: Filter child columns for each parent merge column header

 

Insert a Select tool and select the columns which you want to merge or keep un-merged, as depicted below:

 

AlteryxMatt_6-1659563194788.png 

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

 

Insert a Basic Table tool after Select tool with default configuration like below:

 

AlteryxMatt_7-1659563194815.png

 

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

 

Step 3: Create a method to pass the merge column header name

 

Create a Text Input tool with the column header name for each merged column header like below:

 

AlteryxMatt_8-1659563194838.png

 

Step 4: Create a merged cell header block

 

Insert a Basic Table tool and configure it like below:

 

AlteryxMatt_9-1659563194967.png

 

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

 

Step 5: Align the merged column table to the child table

 

Use a Union tool like below to place the merged cell table above the child table:

 

AlteryxMatt_10-1659563195064.png

 

This specific step will create merged column headers.

 

You can insert a Render tool and write to an Excel file to see the output. [Optional]

 

Step 6: Bring in all the sub-tables to create a single main table

 

Insert Join Multiple tools and join by record position like below.

 

AlteryxMatt_11-1659563195226.png

 

Insert a Layout tool with the default configuration to merge tables in separate columns into a single table like below. The current configuration behavior is to merge columns horizontally into one.

 

AlteryxMatt_12-1659563195291.png

 

Step 7 [Excel]: Phew! Output

 

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

 

AlteryxMatt_13-1659563195448.png

 

Excel report output:

 

AlteryxMatt_14-1659563195484.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:

 

AlteryxMatt_15-1659563195618.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 by now in formats other than Excel you can see the extra line like the one below Same Day row.

 

AlteryxMatt_16-1659563195719.png

 

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

 

AlteryxMatt_17-1659563195738.png

 

We hope this was helpful.

 

We highly encourage you to go and experiment so that you can better understand why we are following this approach.

 

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!

 

Comments