4 Replies Latest reply on Apr 26, 2011 7:10 PM by Joe Mako

    Joining Tables - how hard can it be?

    .George Prevelige

      Every time I try to join tables in Tableau it is a complete disaster. I give up and end up flattening the data manually. But I reached a point where I refuse to give in. No longer will I be a slave to my own ignorance.

       

      So, could someone please tell me where I am going so wrong with something that should be so simple?

       

      Data Table #1

      ID – one row per ID

      Year Created

       

      Data Table #2

      ID – multiple rows per ID

      Cost

       

      All I want to do is: join table #1 to #2 using ID and display a total for Costs by Year Created. No matter what I do I end up with overlapping data elements or weird results. Why can’t I SUM(Cost) via the relationship?????

       

      I harbor a small fear that the solution may involve those completely unintuitive table calcs. Either that or what I’m trying to do is just fundamentally dopey.

       

      Thanks.

        • 1. Re: Joining Tables - how hard can it be?
          Joe Mako

          How about a table calculation like:

           

           

          WINDOW_SUM(SUM([COST].[Cost]))


           

          or

           

           

          IF FIRST()==0 THEN
          
           WINDOW_SUM(SUM([COST].[Cost]),0,IIF(FIRST()==0,LAST(),0))
          END
          


           

          for increased speed of calculation when you have lots of records.

           

          and then set the pill's Compute using to "ID", as done in the attached.

          • 2. Re: Joining Tables - how hard can it be?
            Richard Leeke

            Is there any reason why you can't define do the join in the connection, rather than using 2 connections and joining them with data blending?

             

            If your two tables are in the same database (which includes 2 sheets in the same Excel spreadsheet, or 2 text files in the same directory), you could just define a multiple table connection, joined on ID and hey presto, no need for those nasty unintuitive table calcs.

            • 3. Re: Joining Tables - how hard can it be?
              .George Prevelige

              Thanks, Joe. Your calc worked perfectly in the example file. Unfortunately, my real workbook is way more complex and contains way more data. I haven't been able to get the fix to work in that file. But I will keep trying.

               

              And thanks also to you, Richard, for the suggestion. As luck would have it, I'm trying to join an Excel file to a SQL Server table. I went ahead and imported the Excel into SQL and created a view in the database. Works like a champ. But I still don't understand why I can't see the purpose for much less take advantage of the new Relationship functionality.

              • 4. Re: Joining Tables - how hard can it be?
                Joe Mako

                George,

                 

                I agree, partitioning custom table calculations is not intuitive, but can be very powerful, and I believe can be a good solution. I would be more than happy to speak with you either via skype or any other collaboration tool you prefer, and help enable you to take advantage of table calculations in Tableau to allow you to get the results you are looking for. My email is joemako [at] gmail [dot] com.

                 

                If a table calc is not the best answer, I am more than happy to detail alternatives based on your constraints and specific situation.

                 

                My gut reaction after reading that you are trying to join an Excel file with a SQL Server table is, a data blend and a table calc is a good solution, and the the reason why the formula I provided above is because of partitioning, and I would be glad to share the lessons that I have learned, and how you can apply them to your situation.