3 Replies Latest reply on Mar 31, 2014 12:18 PM by Noah Salvaterra

    Values arent showing for two joined tables

    taylor.johnson

      Hello,


      I was able to do this accidentally, but I am unable to replicate it (attached PDF).


      I have an excel spreadsheet with two different tables on two different sheets (Quotes & Treasuries).  I joined these tables on the 'Date' field.

       

      My goal is to show the 'Spread' field if one was recorded, and the corresponding treasury rates (i.e. 5yr/10yr/30yr).  However, even if there is no recorded spread, I would still like to show the rate.

       

      In short, the PDF shows exactly what I am trying to reproduce, and the twbx is what I'm stuck with.  I have also include sample source data just in case.

       

      Any ideas?

        • 1. Re: Values arent showing for two joined tables
          Noah Salvaterra

          In the multi-table join dialogue there are 3 types of joins represented which correspond precisely to 3 joins available in SQL. Those are left, right and inner joins. When a date shows up in both datasets it will be in all 3 of these joins, so if your datasets correspond 1 to 1 with regard to the join field and there are no missing records it doesn't matter which of these is used. When there are more are multiple matches there are some additional subtleties, but since each of your data sets will have at most one record per date it comes down to what is done when a date shows up in just one of the datasets.

           

          Since you connected to the Quote sheet before the Treasury Rate sheet, Quote is thought of as being to the left of Treasury Rate. This has a bit more meaning if you were to look at the SQL query written out across a single line since the first table would be on the left.

          • A left join will not filter the table on the left, so if there is no match in the other table it will keep the row but any fields coming from the right table will contain nulls. If a row in the right table doesn't have a match, then it will be filtered by a left join.
          • An inner join will bring back only rows which have a match in both the left and right tables, so may filter rows from either the left or right tables if there is no match. Inner join is the default for a multi-table join.
          • A right join is symmetrical to a left join. Left joins get more press, because it is more common to bring in the more comprehensive datasource first, but there is nothing wrong from a database (or an algebraic) standpoint with doing it the other way around. Since you brought the Treasury Rates sheet in second a Right Join matches your description and the pdf. I attached a workbook with this change made, the Join type dropdown in the multi-table join dialogue is also indicated in the image below. To get to this window right click the connection and select edit connection, then click on the treasury rate table and the edit button.

           

          Let me know if this sorts your problem out. Apologies if I over explained, I was just worried you would bring in the tables in a different order next time and be confused why it didn't work anymore.

           

          N.

          RightJoin.png

          • 2. Re: Values arent showing for two joined tables
            taylor.johnson

            Noah,

             

            Thank you so much for the detailed explanation (its very helpful) and twbx!  That worked.

             

            Sincerely,


            Taylor

            • 3. Re: Values arent showing for two joined tables
              Noah Salvaterra

              No problem. Glad I could help.

               

              N.