3 Replies Latest reply on Aug 10, 2018 7:28 AM by Steve Moore

    Outer Join not picking up values in 'right' table

    Steve Moore

      Hello,

       

      I'm a brand new Tableau user, and I'm having an issue with my data source (I think).  I have two tables on which I've performed an outer join - the 'left' table contains Date, Mill, Asset, and # of Complaints, and the 'right' contains Date, Mill, Asset, and Product Produced.  I've made Date, Mill, and Asset my key fields.  From these tables, I'm calculating Complaints per Product Produced.  In some cases I have Complaints with no Product Produced, and in others I have Product Produced with no Complaints.  I'd like for all of these to show in my worksheet.  My issue is that they do not.  My table is built using the Asset from the left join, which for some reason shows null in my data source for the Product Produced that I want to see, so that data doesn't pull in.  Here are a few quick screenshots:

       

      Data Source

      datasource.png

       

      Table

      table.png

       

      So I'd expect to see the 2,649 from the data source show in the table under AC12 / Mill-Asset SU, but as you can see, this is blank.

       

      Any help would be greatly appreciated.

       

      Steve

        • 1. Re: Outer Join not picking up values in 'right' table
          Shinichiro Murakami

          HI Steve,

           

          Difficult to say without seeing data,

          but you should pull the field of Asset and Mill from Main data source (you have two same field respectively).

           

          Thanks,

          Shin

          • 2. Re: Outer Join not picking up values in 'right' table
            Steve Moore

            Thank you for your prompt reply.  I created and attached a sample data set, eliminating some of the extraneous data, to hopefully illustrate my issue.  I would like to create a table from these two data sources (tabs in the Excel sheet) that shows all assets listed in either source.  If there are no complaints for a particular asset (e.g. Assets 3, 4, 5, 7), I would expect these to be listed in my table, and for the 'Complaints per Thousand' to be 0.  If there were complaints but no production (e.g. Asset 10), I'd expect this to be listed and the 'Complaints per Thousand' to be either 0 or indeterminate (depending on how I structure the formula).  I'd also like to do this using whatever generally accepted best practices apply (instead of kludging it).  I've attached a couple of screenshots to illustrate what is happening.

             

            I'm thinking (using my Excel knowledge as a basis) that perhaps I need a third data source that is a list of all of my assets, which then "looks up" data from the other two sources?

             

            Data Source

            datasource.png

             

            Table

            table.png

            • 3. Re: Outer Join not picking up values in 'right' table
              Steve Moore

              That did it!  Much like I'd structure a VLOOKUP table in Excel, I first created a list of Assets (which are unique), then joined this list to a union of the other two data sources.  I've attached my sample file, and below is the screenshot of the table that I've been looking for.  Is there perhaps a more elegant way to accomplish this that doesn't require the asset list?  For now I'm using a flat file to learn, but I'm concerned about the difficulty of maintaining such a list when I switch to live data sources.

               

              Table

              table2.png