4 Replies Latest reply on Feb 29, 2016 2:19 PM by Joe Oppelt

    One to many relationship using 2 data sources

    Jaspreet Narang

      Hi Everyone,

       

      I am having an issue and I am going to explain it with a small subset of the data. I have a tableau file which contains 2 data sources. First data source looks like this:

      Programs.PNG

      And the second data source looks like this:

      Vendors.PNG

      I put a join in tableau using project id as my primary key but I don't see both of the vendor names associated with the specific project id when I put the field on the row shelf.

      Vendor View.PNG

      Please advise how can I solve this.

       

      I have attached the tableau file also.

       

      Thanks!

       

      Regards,

       

      Jesse

        • 1. Re: One to many relationship using 2 data sources
          Joe Oppelt

          When you display data from a secondary source, it has to be some function of the "chunk" of data blended through the join field.  Usually it's something like SUM (for numeric data) or COUNT.


          When you have a text field, Tableau can't automatically SUM that.  And when there are multiple values that fit what tableau wants to display, you get a  ' * ' in the cell.

           

          See the attached.  In sheet 2 I changed the order of primary and secondary sources.  Now you'll get the multiple values of Vendor, but as you can see you get nothing for projects 2-5 because no data for those projects is in the Vendors source yet.

           

          If this is coming from one excel file, you can join instead of blend.  See Sheet 3.  However, the two data sources must be coming from the same file, not two separate files, otherwise you only get the option of blending.

           

          And finally, go back to your Sheet 1.  I changed the vendor name on ROWS to do a MIN.  That will get you the first alphabetical value.  At least you won't get a  ' * ' in your data cell,.


          So these are some options available to you.

          1 of 1 people found this helpful
          • 2. Re: One to many relationship using 2 data sources
            Ben Page

            It depends on how your full data looks. If you're dealing with the same Project IDs in both data sources, and there's only 1 PM per project, you could consider switching around your primary and secondary data sources:

            Tableau aggregates all fields, including dimensions, from a secondary data source. When there is a one to many relationship, you're going to get the *. If it's a one to one relationship, the dimension's value will come through fine. Again, if there's only one PM per Project ID, you can switch around the data sources. With your subset of data, you'll notice that only Project ID 1 comes through when Sheet 1 is the primary. If all of the relevant Project IDs are in the Vendors data source, then this approach might work for you. Otherwise you might need to reshape your data. Here's some more info on troubleshooting data blends:

            Troubleshooting Data Blending

             

            Ben

            1 of 1 people found this helpful
            • 3. Re: One to many relationship using 2 data sources
              Jaspreet Narang

              Hi Joe,

               

              Thanks for your response. I looked at Sheet 3 and tried doing the same method on my file and it does seem to work. The only issue I am having is that I have some fields like Budget, Amount Spent, Duration in Months etc. that gets counted twice, Is that because I have two vendors and the rows are  being repeated twice ? If yes is there a way to solve this issue ??

               

              Regards,

               

              Jesse

              • 4. Re: One to many relationship using 2 data sources
                Joe Oppelt

                If it were me, I would reshape the data.


                See attached

                1 of 1 people found this helpful