2 Replies Latest reply on Jul 25, 2018 7:34 AM by Jasper Li

# Is there any method to group repeated row in a sequence?

Hi all, I want to ask a question about grouping repeated rows in table.

Sequence Order
Value
1A
2A
3B
4A
5A
6A
7C
8

C

Suppose I have a data set as above, and I want to group the result as below:

Group OrderValueCount
1A2
2B1
3A3
4C2

is there any function in tableau which can do this?

Thank you!

• ###### 1. Re: Is there any method to group repeated row in a sequence?

Hi,

There isn't one function to do this, instead we can use three calculated fields. The reason why this is a little more complex is that we need to do row-by-row comparison from the underlying data and the only way we can do that in Tableau Desktop at present is with table calculations.

Here's the workout view: Note that Sequence Order is used as a dimension while Value is used with the ATTR() aggregation. This simplifies the setup of the three table calculations, they all have a Compute Using on Sequence Order.

The Group Order field's formula looks like this:

IF INDEX() = 1 THEN

1

ELSEIF ATTR([Value]) != LOOKUP(ATTR([Value]), -1) THEN

PREVIOUS_VALUE(0) + 1

ELSE

PREVIOUS_VALUE(0)

END

INDEX() tells us where we are in the partition (i.e. what row we are at). LOOKUP() is Tableau's equivalent of a cell reference in Excel, it lets us look at the prior value of the given field which is ATTR([Value]) in this case. And PREVIOUS_VALUE() is a self-referencing function in that it returns the prior value of the *current* field, i.e. Group Order. This lets us build out the group order.

The Count field's formula looks like this, it's much the same as Group Order only it's iterating over the values.

IF INDEX() = 1 THEN

1

ELSEIF ATTR([Value]) != LOOKUP(ATTR([Value]), -1) THEN

1

ELSE

PREVIOUS_VALUE(0)+1

END

(This formulation is necessary because we can't partition table calculations on other table calculations, otherwise we'd use a function like COUNTD() or SIZE()).

Finally the Last Value in Sequence Flag has this formula:

LAST() = 0 OR ATTR([Value]) != LOOKUP(ATTR([Value]), 1)

LAST() is a Tableau function that tells us how many rows we are from the last row in the partition, and the last row is 0. That way we can identify the very last Sequence Order value without having to know what it actually is.

As I mentioned all the table calculations have a compute using on the Sequence Order. Having placed them in the Workout view and validated that they work as expected, then I duplicated that worksheet and rearranged pills to get the final view: I've attached a v10.5 Tableau packaged workbook with the calculations & worksheets. Let me know if you have any questions!

Jonathan

2 of 2 people found this helpful
• ###### 2. Re: Is there any method to group repeated row in a sequence?

Hi Jonathan,

Many Many Thanks for your clear and helpful explanation