3 Replies Latest reply on Jun 24, 2018 2:15 PM by James Alaly

    Grouping data and aggregating string fields

    James Alaly

      Is it possible when grouping data to aggregate a string field by appending the strings from grouped data together?  I do not see this option in the aggregate parameters.  Example below.

       

      IDCodeValue
      1D1.1
      1C1.5
      1E2.6
      2C1.8
      3D1.2

         

      Grouping by ID and Aggregating by Code and Values would produce:

      IDCodesTotal Value
      1D C E5.2
      2C1.8
      3D1.2
        • 1. Re: Grouping data and aggregating string fields
          Joshua Milligan

          James,

           

          Currently, there really is no good way to do this.  You might try adapting the approach here (Unpivot in Tableau Prep (Rows to Columns) | VizPainter ) to transform the rows to individual columns and then concatenate them together as a single string.  There is no way to control the order, though, so you might likely end up with D C E  or E D C.  Also, you'd have to know the possible values and the maximum number of rows possible per ID and create that many calculated fields.  So, a lot of assumptions on getting this approach to work.

           

          If your original source is a database that allows for Custom SQL, there might be options for doing the transformation that way.

           

          Otherwise, please:

          • Create an idea to let the developers know this aggregation would be a very useful feature add (if you don't create it in the next few days, I will )
          • Vote up this idea for unpivot: https://community.tableau.com/ideas/8707.  It's not an exact solution, but if the feature were implemented well, it would give you a tool towards the solution.
          • Vote up this idea for table calcs in Tableau Prep: https://community.tableau.com/ideas/8732.  PreviousValue() would allow you to build the full concatenated string and then keep the record that had the longest string.

           

          Hope that helps!

          Joshua

          • 2. Re: Grouping data and aggregating string fields
            James Morse

            This feature is more like a SQL aggregate function vs a window function (or table calc in Tableau jargon).  Many major databases now support string list aggregation like this.  One thing to add about the feature need that can add even more complexity is when you want your concatenated list value to be a distinct list (i.e. set) rather than a full bag of values and your data isn't already aggregated to column for which you want to generate the concatenated list value.

             

            In your example, think about if your data had an additional row like:

            ID = 1

            Group = D

            Value = 50.0

             

            Your concatenated list value would become "D C E D" without the ability to pull the distinct list.  In my experience, this is rarely the result you want.  In Oracle using the LISTAGG function for example, you must first aggregate the data to the level of the column for which you want to generate the list (i.e. ID AND Group), and then run a string list aggregation function, grouping this time without the column you want to generate the list.  This middle step is a huge pain.

             

            +1 for both adding a string list aggregate function as well as window functions to Prep and/or Desktop!

            • 3. Re: Grouping data and aggregating string fields
              James Alaly

              Thanks guys, I posted an idea.  As an aside, James Morse, my application actually does need duplicates to be included but I can see how usually that would be undesirable.