12 Replies Latest reply on Mar 15, 2017 2:48 PM by Antigone Oreopoulos

    Concatenating data from child rows

    Martin O'Rourke

      Hi,

       

      I am working with a data source with a standard one-to-many relationship i.e. an opportunity, having one or more line items for each individual product (as below).

      tableau-1.jpg

      I need to concatenate the individual line item product names into a single field for each opportunity (again, as below).tableau-2.jpg

      I have tried doing this as part of a Custom SQL Query - in other words, performing the concatenation at the point of data gathering - but to no avail. Regardless of the method tried (CONCAT, XML PATH, etc) I am seeing generic query failure messages. I am also not familiar with any way of doing multi-row concatenation in a calculated field.

       

      I am not sure if this is even possible, but would appreciate any feedback/guidance!

       

      Regards,

      Martin

        • 1. Re: Concatenating data from child rows
          Tracy Rodgers

          Hi Martin,

           

          The only way I can think of doing this is quite manual, but it does give the desired result. Create a calculated field like the following:

           

          if [Opportunity ID]=123 then 'Product A, Product B'

          elseif [Opportunity ID]=456 then 'Product B'

          elseif [Opportunity ID]=789 then 'Product A, Product B, Product C' end

           

          Hope this at least gives some insight!

           

          -Tracy

          • 2. Re: Concatenating data from child rows
            Martin O'Rourke

            Many thanks for the suggestion, Tracy!

             

            Unfortunately, the solution needs to be scalable - it would be dealing with a live connection and thousands of records, so hard-coding IDs and combinations isn't really an option!

             

            At the moment, it seems as though doing the concatenation at the Custom SQL Query level is the best (only?!) conceivable approach, so it may be a case of finding the correct methodology there. Will keep digging!

             

            Regards,

            Martin

            • 3. Re: Concatenating data from child rows

              Hi Martin,

               

              which database server do you use? If you use MySQL you can use GROUP_CONCAT to achieve exactly what you want.

               

              If you're using MS SQL, try looking at the following link. http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005

               

              Eljakim

              • 4. Re: Concatenating data from child rows
                Martin O'Rourke

                Hi Eljakim,

                 

                Thank you for replying!

                 

                My eventual intention is to connect to live Salesforce.com data (using an ODBC connector) but I unable to even achiveve a working example using a custom SQL query on any data - Excel spreadsheet, etc. I have tried a number of the solutions on the link you provided, but am seeing generic syntax errors and/or failure messages (without any clue as to what's causing the problem).

                 

                Cheers,

                Martin

                • 5. Re: Concatenating data from child rows
                  Richard Leeke

                  You can do this very easily with Tableau table calculations (not that anything is ever easy with table calculations - but this is at the easy end of the scale).

                   

                  I've attached an example using the Superstore Sales sample data source. I'll take you through it step by step.

                   

                  The first sheet just shows the data that we have to work with: customers, with multiple orders per customer and multiple items per order. I'm just showing the product name for each order item. Note that the results returned from the database have one row per order item.

                   

                  The second sheet has moved the [Product Name] field to the Level of Detail shelf. This ensures that Tableau is still returning the same rows from the database. I've also added the calculated field [All Products for Order] onto the view. Actually the field name doesn't look appropriate at this stage, because what you will notice is that the first item for each order just has the Product Name for that order item, the second row has the first two products concatenated together, and so on up until the last row for the order which has the result you are after - all items (i.e. all product names) concatenated together. I'll explain the calculation for that in a minute, but first lets look at sheet 3 and see how we get the answer you are after.

                   

                  On sheet 3 I've added a a field called [Last Row for Order?] to the filters shelf and selected only rows for which that field returns True. That field is another (very simple) table calculation, defined like this:

                   

                  LAST() == 0

                   

                  That is simply testing to see if the offset to the last row in the partition is zero (which is clearly only true if we are on the last row). I have the field configured so that the partition means all the rows for a single customer order. I did that by clicking on the drop-down arrow on the calculated field pill on the filter shelf and setting Compute Using to [Product Name]. That means perform this calculation over all values of [Product Name] for each permutation of the other dimensions (i.e. do the last row calculation across all product rows for each customer order).

                   

                  Placing a table calculation on the filter shelf like that causes Tableau to filter the rows that are displayed, but without affecting the rows that are brought back from the database. So all of our products are still available for use in the other table calculation.

                   

                  One thing to note. That filter may not work correctly unless you are on the latest maintenance release of Tableau (7.0.4). There was a subtle change in the way that partitioning can be defined for fields on the filter shelf in that release. I can't check the behaviour without falling back to an old version.

                   

                  Now back to the [All Products for Order] calculation. That is defined like this:

                   

                  PREVIOUS_VALUE("") + IIF(INDEX()==1, "", ", ") + ATTR([Product Name])

                   

                  The key is the PREVIOUS_VALUE("") function which can just take a bit of getting your head around. That simply returns the result of the current calculation from the previous row, or an empty string ("") if this is the first row in the partition. So the calculation takes the previous value, adds a comma unless we are on the first row and then appends the value of [Product Name] for the current row.

                   

                  Once again, you need to set the partitioning to Compute Using [Product Name], exactly as I explained before.

                   

                  And that's all there is to it. 

                  5 of 5 people found this helpful
                  • 6. Re: Concatenating data from child rows
                    Martin O'Rourke

                    Hi Richard,

                     

                    Thanks a million for taking the time to step through this with me - it is exactly what I am looking for!

                     

                    I had been putting off upgrading to the latest release, but this is as good a reason as any to just do it!

                     

                    Once again, thank you!

                    Martin

                    • 7. Re: Concatenating data from child rows
                      Richard Leeke

                      BTW, you don't need the latest release to do this - it's just that prior to 7.0.4 you can't have a table calculation on the filter shelf unless you first put it on another shelf and set the partitioning and then copy that to the filter shelf. It's just a bit cleaner now that you don't have to do that, so I thought I'd demonstrate the clean solution.

                       

                      But don't let that be a reason to put off upgrading...

                      • 8. Re: Concatenating data from child rows
                        Mira Shen

                        Hello, Richard,

                         

                        I am trying to create a legend showing multiple selections in one field. This is somewhat similar as what you have done here. I tried a bit following your workbook but it didn't work quite well for me.

                         

                        1\ for the workbook of legend example2 -

                        - Tab of "Table2" looks as what I want but after I export everything looks very different (they are actually in 2 rows).

                        - I tried a bar chart based on "Table2", it seems working. However, if I changed the BAR to LINE it was screwed up: Legend showed A1\NULL and A1\D1,D2, which is not what I want.

                         

                        2\ I tried this on my real data. I even couldn't get a table like "Table2" in legend example2 work book. I kept getting two rows for  concatenated products: NULL and combination (see attached screenshot). I THINK I did exactly same thing:)

                         

                        Would you please kindly take a look and let me know anything that I missed?

                         

                        Thanks.

                        Mira

                        • 9. Re: Concatenating data from child rows
                          Saloni Rai

                          Hi Richard,

                           

                          This is great!

                          I have a similar problem and hope you can help.

                           

                          1) The concatenation is not working for me. I think it is because the "Previous" value is never picked up? My data is not sorted in any order. I see in your reply you say at the end "Once again, you need to set the partitioning to Compute Using [Product Name], exactly as I explained before."

                          Where do I do that?

                           

                          2) How can I ensure the "All sequence" is ordered by a timestamp in the row?

                           

                          3) I would like to compute this sequence by order ID, and then use it as an input into other calculations. That is, I'd like it to be a calculated field for each order ID and then do some string/pattern matching to create groups that fit a certain need. Will that be possible?

                           

                          Thanks!

                          • 10. Re: Concatenating data from child rows
                            Dhiraj Malik

                            If the solution is not working for anyone please make sure both the calculated fields [Last Row for Order?] and [All Products for Order] are computed using the Product Name field.

                             

                            Once the product name field is moved to the detail shelf it will appear in the "compute using" list.

                            • 11. Re: Concatenating data from child rows
                              Tableau Hostinger

                              Hi Richard,

                               

                              Do you know of any solution allowing me to analyze the concatenated data? Given the Martin's example, I would want to see how many of each variations are available in the dataset.

                              • 12. Re: Concatenating data from child rows
                                Antigone Oreopoulos

                                Thanks very much for this solution.   It worked beautifully, but effected 2 other things on my sheet.

                                 

                                Prior to creating and adding this new list field, I had a Row Number [index ()] and Row Count [size()].

                                 

                                These no longer work as expected anymore and give strange results.  I tried changing the calculation to compute differently, but all the combinations I tried did not work.

                                 

                                Any thoughts on this?

                                 

                                regards