04-30-2018 12:54 PM - edited 07-21-2021 01:46 PM
In this post, a user asked if you can merge cells in the Table Tool like you can in excel. There are lots of ways you can get creative with the Table Tool, for example you can have your report span across the page in multiple columns as described here or you can add totals to your data to add to your report or create headers and sub-headers as described here.
So, how do you merge cells using the Table Tool itself? You can merge cells by nesting a table inside another table:
Tables can be nested more than one deep, you could start off with city, roll up to region, and then to state:
To clean up the doubled up header rows that are created when nesting tables like that, deselect "show column headings" in the Table Tools' settings:
.... and create the header row in a separate Table Tool. In this example, we are also changing the background color and making the font bigger, and changing the wording to "Region" for the Region column:
Lastly, there should also be subtotals for each region.As with anything in Alteryx, there are different ways of doing this,they could be added as described inthis post,or the process used to create the headers can be used. First, the data is summed to create totals, then the totals are put into tables grouped by region:
Lastly, the table snippets are combined in a Layout Tool using the "Vertical Merge Contents to Line Up Table Columns" option:
Also see the attached v2018.1 workflow for the complete example.
Could you please add the input files? C:\Program Files\Alteryx\Samples\en\SampleData\CO Store File*.yxdb
The file you are requesting comes with the Alteryx software. This is our sample data. You actually should be able to find that file in the location you have added to the post if you have Alteryx loaded on your C:drive.
Thanks - I was able to find the local files which were nested deep in a completely different path than the obvious file location.
1) The merged vertical cell renders in the Browse Tool, but does not output to Excel. See screen shot below.
2) How would you modify this to create a merged title cell that sits across the width (columns) of the table...and exports to Excel?
It looks like Excel is ignoring the merged cell under Region. Instead Region is populated by the values from City, except for the first cell -- which is the Region value.
Hi @hellyars
It seems that excel is ignoring the two nested tables and treating them as one.
I got this to work by not nesting the tables anymore, adding region to the table (also grouping by it to preserve the subtotals) and adding a column rule that makes all but the first row blank.
That doesn't merge cells but it doesn't repeat the value over and over so it looks decent.
So When Row # != 1 then use a blank replacement text:
Awesome article @HenrietteH , thanks for that!
I'm curious about the "column header merge" like with the totals. The total at the bottom get merged spanning "Region" and "City"(blue pen). Is it possible to span the cells at the end, e.g. "Summit County" and "Weld County" (red pen)? It seems like the layout tool just merge the first cells?
The only solution I got is to manipulate the PCXML code and move the colspan="2" from column 0 to the end. Not that handy. I think this gets determined within the layout tool orientation option "Vertical Merge COntents to Line up Table Columns".
For future workflow devs researching this topic, perhaps this video will help in addition to Henriette's wonderful explanation.
If saving in C, the file can be located here: C:\Users\firstname.lastname\Downloads\FinishedAnalyticApp\_externals\1\Master Store File - CO.yxdb