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?

    Jasper Li

      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?
          Jonathan Drummey

          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:

           

          Screen Shot 2018-07-25 at 9.47.03 AM.png

           

          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:

           

          Screen Shot 2018-07-25 at 9.53.35 AM.png

           

           

          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?
            Jasper Li

            Hi Jonathan,

             

            Many Many Thanks for your clear and helpful explanation