2 Replies Latest reply on Dec 28, 2018 2:06 PM by Joshua Milligan

    Tableau Prep: Concatenating many to one into new calculated field

    Martin Sellers
    Martin Sellers Nov 29, 2018 1:32 PM

      Hi-

      I am working in Tableau Prep and am wondering how to created a specific kind of calculated field output.  In the below example, my aim is to create a new calculated field that will concatenation all "schedule_codes" that have the same "full schedule".

      The many to one relationship is "Full Schedule"----> "Schedule_Code"

       

      For example, in the highlighted grouping shown below (2 rows), for every full schedule "Sunday (06:00-00:00-......)", I want the new calculated field to produce "XIJ, XQ1".

       

      I want this to happen for all possible groupings many to one relationships.

      Thanks!

       

       

      Capture.PNG

        • 1. Re: Tableau Prep: Concatenating many to one into new calculated field
          Rodrigo Calloni
          Rodrigo Calloni Dec 28, 2018 10:32 AM (in response to Martin Sellers)

          Hello Martin

           

          Here is one option that may work:

           

          1- Split the Full Schedule field using the ) as delimiter to the split

           

          2 - Add a Pivot Step and pivot all split fields. This will create one column that contains every daily schedule and they will be related to each schedule_code

           

          3 - You may have to delete some empty rows that may result of the pivot.

           

          Note: If you could copy and paste the two rows from your dataset into an Excel/CSV file and share I could try with the data and see if that really works.

           

          Rodrigo

          • 2. Re: Tableau Prep: Concatenating many to one into new calculated field
            Joshua Milligan
            Joshua Milligan Dec 28, 2018 2:06 PM (in response to Martin Sellers)

            Martin,

             

            This is a tough one without an easy answer.  Jonathan Drummey created this idea for Tableau Desktop, though ideally it would apply to Prep as well: https://community.tableau.com/ideas/4538

             

            Here are some thoughts:

            • If your data source is SQL Server, you should be able to use Custom SQL in Tableau Prep to meet your goal (see sql - Multiple rows to one comma-separated value - Stack Overflow for a technique).  There are likely ways to accomplish this with other relational databases that allow for Custom SQL.  You could use a Custom SQL Input and then join the results to your main dataset.
            • Using a few Aggregate steps (or duplicates of the Schedule Code field in a single Aggregate step), you can get the MIN, MAX, and Count Distinct schedule codes per unique schedule. That would allow you to join back into the flow, and employ some logic.  Where the distinct count was 1, you could keep the value.  Where it's 2 you can have Min + "," + Max.  Where it's 3 or more you can have Min + "," + Max + " and " + STR(Count - 2) + " others."
            • Consider voting for this idea, which would provide a possible solution: https://community.tableau.com/ideas/8732
            • If you are going to visualize this data in Tableau, consider leaving your data structure as is.  You can build a comma-delimited list with a table calculation or even just put Schedule on Rows and Schedule Code on Text in the Marks card.  Filtering and a few other things will be easier with your existing structure as opposed to building a delimited list.

             

            Hope that helps!

            Joshua