Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
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.”
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
2. How to Create an Alteryx Report with Merged Columns (you are here)
3. How to Create an Alteryx Report with Both Merged Rows and Merged Columns
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.
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:
Step 2: Create child tables for each parent merge row cell
Insert a Basic Table tool after Select tool with default configuration like below:
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:
Step 4: Create a merged cell header block
Insert a Basic Table tool and configure it like below:
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:
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.
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.
Step 7 [Excel]: Phew! Output
Add a Render tool and configure it to write it to Excel.
Excel report output:
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:
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.
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!
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!
Read Part 3 Here.
