Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Merging cells with the Table Tool

HenrietteH
Alteryx
Alteryx
Created

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:

1-4-2018 5-26-34 PM.png

 

Tables can be nested more than one deep, you could start off with city, roll up to region, and then to state:

1-9-2018 11-10-05 AM.png

 

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:

 

4-25-2018 3-54-11 PM.png

 

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

 

1-9-2018 11-23-43 AM.png

 

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:

 

4-25-2018 3-36-53 PM.png

Lastly, the table snippets are combined in a Layout Tool using the "Vertical Merge Contents to Line Up Table Columns" option:

 

4-25-2018 3-49-21 PM.png

 

Also see the attached v2018.1 workflow for the complete example.

Attachments
Comments
djoyus1
8 - Asteroid

Could you please add the input files?  C:\Program Files\Alteryx\Samples\en\SampleData\CO Store File*.yxdb

DanM
Alteryx Community Team
Alteryx Community Team

@djoyus1,

 

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.

djoyus1
8 - Asteroid

Thanks - I was able to find the local files which were nested deep in a completely different path than the obvious file location.

hellyars
13 - Pulsar

@HenrietteH   

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?

 

Screen Shot 2021-02-19 at 11.39.50 AM.png

 

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.  

HenrietteH
Alteryx
Alteryx

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: 

 

 

2021-02-22_17-01-48.png

MarvMaster
6 - Meteoroid

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?

 

 

MarvMaster_0-1616774256067.png

 

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".

 

 

Animanga008
8 - Asteroid

For future workflow devs researching this topic, perhaps this video will help in addition to Henriette's wonderful explanation.

 

linda_rydalv
Alteryx Alumni (Retired)

If saving in C, the file can be located here: C:\Users\firstname.lastname\Downloads\FinishedAnalyticApp\_externals\1\Master Store File - CO.yxdb