8 Replies Latest reply on Feb 8, 2017 2:37 PM by Joshua Milligan

    Outer Join (I think)

    Adil Al Raeesi

      Hi everyone,

       

      I think I need an outer join to accomplish the following, but I'm not sure whether there is a better way / a hack for achieving the desired outcome.

       

      I'm on Tableau 10.1, using an extract of a few MS Access Tables.

       

      I have three tables that I am trying to join as follows

       

      MASTER CLASSIFICATION TABLE

      This table contains a list of all our projects, along with their meta data.

       

      Project Number (this is my Join field)Metadata 1Metadata 2

      A

      B
      C
      D

      E

       

      PROJECTS BUDGETED

       

      Project NumberPeriodRevenue Bud.
      AJan 2017
      BFeb 2017

       

      PROJECTS ACTUAL

      Project NumberPeriodRevenue Act.
      AJan 2017
      ADec 2016
      CJan 2017

       

      I need to Join the Budget and Actual Table to the Master Classification table in such a way that projects A, B and C are presented as dimensions in my Viz, so that I can work out Delta Revenue. Project B would be a project that we had budgeted, but never eventuated and project C would be an example of a project that has been engaged which was never accounted for in the budget.

       

      I'm guessing I need an outer join to achieve the above? Is there another way?

       

      Thanks everyone!

        • 1. Re: Outer Join (I think)
          Jim Dehner

          Hi 

           

          You will probably want to look at blending the 3 sources - The primary would be you Master Classification and the other 2 would link to it on the project number

           

          at the link below you will find a recorded Blending Traing video - it is very good and covers the subject

           

          Let me know if this helped

           

          Jim

           

           

           

          Advanced Live Training - Data Blending | Tableau Software

          • 2. Re: Outer Join (I think)
            Joshua Milligan

            Adil,

             

            If all three tables are in the same Access database, you shouldn't have any issues joining them together in Tableau:

             

            I would recommend Master Projects as the first table and then join Actual and Budgeted to that using Left joins on the Project field.  That way, you'll have all the projects for your analysis.

            The names of the fields are a bit cleaned up here (you can rename using the drop down arrow next to the field name in the preview).

             

             

            After you've created the connection, a few calculations will help:

             

            1. You'll have a few rows that you don't want: where the periods don't match.

             

             

            Create a field named Mismatched Periods with the code:

            IF NOT ISNULL([Actual Period]) AND NOT ISNULL([Budget Period])

                 AND [Actual Period] <> [Budget Period]

            THEN "Mismatch"

            ELSE "Okay"

            END

             

            Use that as a data source filter and exclude the Mismatch rows

             

             

            Another calculation will help you identify whether projects have been budgeted, eventuated or both:

            Project Status

            IF ISNULL([Project (Actual)]) AND NOT ISNULL([Project (Budgeted)]) THEN "Budgeted Only"

            ELSEIF NOT ISNULL([Project (Actual)]) AND ISNULL([Project (Budgeted)]) THEN "Not Budgeted"

            ELSEIF ISNULL([Project (Actual)]) AND ISNULL([Project (Budgeted)]) THEN "Not Budgeted or Eventuated"

            ELSE "Budgeted and Eventuated"

            END

             

            You can use that as a dimension to filter to the statuses you want to analyze.

             

            To merge the Actual and Budget Periods into a single field you can easily use, create:

            Period

            IFNULL([Actual Period ], [Budget Period])

             

             

            And finally, a calculation to give you the difference between budget and actual:

            Difference

            ZN(Budget) - ZN(Actual)

             

             

            Now, you've got a data source you can work with!

             

             

            Hope that helps!

            Joshua

            2 of 2 people found this helpful
            • 3. Re: Outer Join (I think)
              John Sobczak

              Joshua,  Shouldn't the last two tables be joined to each other based on BOTH Project number AND Period to keep one to one?

              • 4. Re: Outer Join (I think)
                Joshua Milligan

                John,

                 

                I think that would lose ones that had been budgeted but not eventuated or vice-versa since there is no guarantee that the projects are in both tables.  So even a left join wouldn't ensure you ended up with everything.  Or is there something I'm not seeing?

                 

                Best Regards,

                Joshua

                • 5. Re: Outer Join (I think)
                  Joshua Milligan

                  Just to elaborate, if you joined like this:

                   

                   

                  on Budgeted and Actual matching on Project and Period, then you'd still have all the projects, but you'd lose the Actual values for Project C because there were no Budgeted records.  So what should be:

                   

                   

                  ends up being:

                   

                  And this would work the other way around if you put Actual first and then Budgeted.

                   

                  But, I'm definitely open to finding out that I've missed something!

                  Joshua

                  • 6. Re: Outer Join (I think)
                    Adil Al Raeesi

                    Hi Jim,

                     

                    Unfortunately this didn't work for me. The blend seemed to be duplicating some of my data!

                     

                    Thanks though.

                     

                    Adil

                    • 7. Re: Outer Join (I think)
                      Adil Al Raeesi

                      Hi Joshua,

                       

                      This worked perfectly! Thank you so much for taking the time to replicate the table and providing a detailed solution. I also liked the 'add-on' suggestion that you made about the 'Project Status' field, I've incorporated this into the visualisation too!

                       

                      PS I looked in your book 'Learning Tableau' to see if there were any clues in there prior to writing on this forum... Obviously I didn't join the dots, it's there under 'Fixing Data Issues'! Just not spelled out to me like you did in your response.

                       

                      Thanks again!

                       

                      Adil

                      • 8. Re: Outer Join (I think)
                        Joshua Milligan

                        Adil,

                         

                        You're very welcome!  I'm very glad the solution worked!  And thank you also for sharing about your experience with the Learning Tableau - I'm excited to hear when others have been referencing the book and finding it helpful.

                         

                        Warm regards,

                        Joshua