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 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.”
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)
2. How to Create an Alteryx Report with Merged Columns
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.
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.
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:
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:
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:
Insert a Basic Table tool and configure it as:
Now use the union tool to add the headings on top of the data tables. Make sure to configure the output order accordingly.
Step 4 [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 that in formats other than Excel, you can see the extra line like the one below the Technology row.
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!
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.
