5 Replies Latest reply on Nov 15, 2015 9:32 AM by alexander.turner.0

    Table Join Question

    alexander.turner.0

      Dear all,

       

      I have 2 tables as follows:

       

      Table A

      Student Names (unique)

      September Tuition Fees

      October Tuition Fees

      November Tuition Fees

       

      Table B

      Student Names

      Date

      Payment

       

      I'm trying to create a report as follows:

          

      Studen NameSeptemberOctoberNovember
      Student ATuition Fees
      Receipts
      Balance
      Student ATuition Fees
      Receipts
      Balance

       

       

      The issue i'm having is that there are students who have never made a payment and as such there is no record of them on Table B. Therefore although I have selected a left join (include all of Table A) Tuition Fees are empty on all Students that have not made a payment. This is of course wrong.

        • 1. Re: Table Join Question
          Rajeev Pandey

          Hi Alexander

           

          Why arent you are doing Inner join or Right Join.Probably this will solve your problem

          • 2. Re: Table Join Question
            alexander.turner.0

            Hi Rajeev,

             

            None of the options work. As a left join I get all the student names but the table is populated with NULL for those students where there are no payments on Table B.

             

            I created a calculated field such as:

             

            If [date] = NULL (e.g. there is no payment on table B) then just show 99 (to try and debug). But this didn't work either.

            • 3. Re: Table Join Question
              andre.sankari

              Hi Alexander,

               

              Before you do a join you will want to reshape your Table A a bit, because at the moment it is a crosstab and this will prevent you from easily creating a join using the right dimensions. You will also want to make sure that the month when the tuition fee is due and the month when the payment was made are also in the same format (because they need to make part of a join condition between the two tables).

               

              Once you have your dates in a consistent format you can reshape your data. If your data is in an excel file then you can have a look here about how to easily do that.

               

              Then you can do a left join as you initially intended with two conditions:

              A.Student = B.Student

              A.PaymentDue = B.PaymentDateMonth

               

              For your convenience I've attached:

              Example source data.xlsx which contains Table A before and after the reshape and Table B

              Example.twbx where you can see how the data source was built

               

              Hope this helps

              1 of 1 people found this helpful
              • 4. Re: Table Join Question
                Rajeev Pandey

                Yep this is the right approach, We can do the same thing by using the Pivot Feature as well which is available in tableau 9.Thanks for your solution

                • 5. Re: Table Join Question
                  alexander.turner.0

                  thanks Andre,

                   

                  The problem is I cant change the data.

                   

                  Figured a way that this works though without reshaping, as follows:

                   

                  I've created 2 calculated fields:

                   

                  xBalance:

                  SUM([Payments])-AVG(All Month Tuition Fees)

                   

                  Final Balance:

                  IF ISNULL(([xBalance])) THEN

                      -(AVG([All Month Tuition Fees]))

                  ELSE

                      ([xBalance])

                  END

                   

                  its a bit long winded but does the trick. Of course your version would be a lot slicker.

                   

                  thanks again.