WeeklyAlteryxTips#24 Merging cells with the Table tool part2


Continuing from last time, let’s see the Table tool. This time, I would like to do the data part and for the header, use the one you created last time.

The Data that we handle this time

The data we want to handle is as follow.

For example, if you put that data into the Table tool without “Show Column headings” option, you can see the table below.

When you see this table, you want to merge the most left column vertically. It means that it looks like the figure below.

But strictly speaking, the most left column is not merged, this is a work around. So, if the same word comes, it is just erased.

How to erase the same word as the previous row

There are many ways to do that, so I’d like to explain four ways.

1. Use the Tile tool to keep only rows with Tile_SequenceNum 1 and erase the others

This way is very easy and cool. Use the Tile tool and if Tile Sequence Num is not 1, you just erase the category field value.

The Tile tool setting is as follows. Tile Method should be set to “Unique Value” and Unique Column should be set to “Field 1” which contains category field in this case.

An then, use the Formula tool. This is a very simple way because if Tile_SequenceNum field is 1, leave the category field’s value, if not, erase it. The actual formula is as follows.

IF [Tile_SequenceNum]=1 THEN [Field1] ELSE "" ENDIF

This is exactly no code!

2. Sort in reverse order and if the value is the same as the previous row, erase that

This way is using a simple formula. In order to use the simple formula, use the Sort tool. But when there are too many records, it will take more long time than other ways. However, I think this method has the advantage of simplifying the formula and making it difficult to make mistakes.

The way is follows.

  1. Add the Row ID by the Record ID tool in order to hold the row order.
  2. Sort the Record ID field in reverse order
  3. Erase the value in category field if the next row’s value is the same as current row.
  4. Sort the Record ID field in normal order

Generally speaking, sorting is heavy task, but the value of the sorting twice is to simplyfy the formula in the Multi-Row Formula tool. Actually, the formula is follows.

IF [Row+1:Field1]=[Field1] THEN "" ELSE [Field1] ENDIf

The formula above means as follows.

  1. If the value on the next record the same as the current value, erase it
  2. If not, leave it

The formula is very simple!

3. Add delete flag and erase the values later

Since directly deleting the values in the category row would complicate the formula, another method is to create a deletion flag column and erase it later according to the value.

The expression as the Multi-Row Formula is not a kind of complicated.

IF [Row-1:Field1]=[Field1] THEN "TRUE"
  1. If the previous row’s value is the same of the current value, set “True”
  2. If not, set “False”

And then, you can just delete the category value if the flag field is false using the Formula tool.

IF [IsDelete] THEN "" ELSE [Field1] ENDIF

That expression is very simple and there is no demerit.

4. Use the Multi-Row Formula with the complex formula

This way is fewest tools, but the formula is complex. Although it looks elegant, it may not necessarily be the best solution if people are not familiar with Alteryx.

The expression is as follows.

IF [Field1]!=[Row+1:Field1] AND [Row+1:Field1]!=[Field1] THEN "" 
ELSEIF [Field1]!=[Row-1:Field1] THEN [Field1]

It means…

  1. The previous row, current row and next row are not the same, erase the current row’s value
  2. If the current value and the previous value is the same, leave it
  3. If not, erase it

Multi-Row formula watches records from top to bottom, but you can only manipulate values in the current row at any given time, which makes things a little complex. In this case, if you erase the current row’s value, it becomes blank when you move to the next row, so you can’t use the logic of erasing if the current row and the row above are the same (because it is already blank). I think it can be quite troublesome to build a formula while taking this into consideration.

Combine data part and header part

The data part can be created using any of methods 1 to 4 above.

Delete the unnecessary fields at the Table tool. That setting is as follows.

This shows the table below.

And let’s combine it to the header which you made at the Weekly Tips #23. Union those fields using the Union tool as follows.

Of course, you need to adjust the order that the data part is the last order. This makes you the table below.

Sample workflow download

Next Blog post

I spent more time in the article than I expected on the logic of erasing to blank, so next time I’ll explain the width of the table.


Copied title and URL