6 Replies Latest reply on Aug 7, 2018 8:33 PM by Abby Palcer

    Blending Questions

    Abby Palcer

      I am trying to blend two tables for a medical database.


      Doctor Table

      DoctorIDFirst NameLastName
      25SamSmith
      45JudyJane
      55BIllThompson
      65FredJohn

       

      Table A:  (Table A should have 1 record to Table B many records)

      IDDateDoctorIDProcedureAmount
      12017040545AAA2000
      22017061765BBB3000
      32017112525CCC4000

       

       

      Table B

      IDDateDoctorIDPaymentTypeAmount Paid
      12017060145Cash50
      22017082565Check100
      32017110565Credit Card500
      42017121545Cash150

       

       

      I am looking Display something like this in my report:

       

      DoctorA.AmountB.Amount Paid B.Amount Paid - A.AmountHeader 5
      4520002001800
      6530006002400
      254000NULL (or 0 since there is no payment)0

       

       

       

      What I am currently getting.....  I loose Doctor ID 25, since nothing is in Table B.

       

       

      DoctorA.AmountB.Amount Paid B.Amount Paid - A.AmountHeader 5
      4520002001800
      6530006002400

       

       

       

      Blend does not work the way I expected it. If I tried to join then the 1 to many  cause issues with calculations.

       

      I tried to build something out using the Doctor Table first and have the other tables left join to the doctors. This also throws my calculations off due to the 1 to many. What blows is that I need to have two different filters for the date.  When this was done in sql, it was done using temp tables and then doing a left join back to the doctor table.

       

       

      Any suggestions? I am very new to Tableau, so I might be missing something obvious.

        • 1. Re: Blending Questions
          Mavis Liu

          Hi Abby,

           

          You can still use a blend, but use your Doctor Table as your Primary data source.

           

          The reason why you're not able to pull back 25 is because you're using table B as your primary data source.

           

          A blend is not a join, but it follows the same logic as a left join. Where your primary table is your left table.

           

          Thanks,

           

          Mavis

          • 2. Re: Blending Questions
            Mavis Liu

            Hi Abby,

             

            To choose which is your primary data source, just click on the data source in the top left and start building your brand new worksheet using this data source. There should then be a blue tick next to to this data source to show that it is the primary source.

             

            Then clicking on the other data sources and using the fields should add an orange tick next to the source to show that it is a secondary source.

             

            2018-08-07_20h21_49.png

             

            Please see the attached workbook.

             

            Thanks,

             

            Mavis

            • 3. Re: Blending Questions
              Abby Palcer

              I tried to blend starting with the doctor table.

               

              Primary - Doctor

              Secondary - Table A

              Secondary - Table B

               

               

              I think the blend the is ok?   I am starting to wonder if it is the filters.  If you notice that Table A has a Date and Table B has a date.  The dates have two separate definitions.  The calulcation I am trying to do is to get all records from Table A within a given date range.  And I need to get all records from Table B within the same date range.

               

              If I put a filter for 11/2017 through 12/2017

               

              I loose row 25 because I put TableA.Date filter on 25 is still there. Once I put on TableB.Date Filter I loose 25 because 25 has no date within that date range.

               

              I would expect to see...

               

              Doctor

              A.Amount

              B.Amount Paid

              B.Amount Paid - A.Amount

              Header 5

              450150150
              650500500
              254000NULL (or 0 since there is no payment)-4000
              • 4. Re: Blending Questions
                Mavis Liu

                Hi Abby,

                 

                Yes the dates is probably it. Can you use two parameters? A start and end date and then use calculations as filters to retrieve the data from all the tables which are between the start and end dates?

                 

                Thanks,

                 

                Mavis

                • 5. Re: Blending Questions
                  Abby Palcer

                  I tried to create a new date parameter.  In each data source, I created a new calculated field that checks to see if each date was within the date range.

                   

                  For example -

                  Secondary Source Table A

                  Table A. Date >= StartDate and TableA.Date <= EndDate

                   

                  Secondary Source Table B

                  Table B. Date >= StartDate and TableB.Date <= EndDate

                   

                  Then I  selected True for both and dropped then into the filter. I get the same results.  The record is dropped once I add the check for TableB.Date.

                   

                  I am so lost right now.

                  • 6. Re: Blending Questions
                    Abby Palcer

                    I think the problem is the join.

                     

                    In SQL when I do something like this. I will loose any doctor that is on the doctor table but not on table a.

                     

                    Select Doctor.*

                    From Doctor

                    Left Join Table A

                      on Doctor.ID = A.DoctorID

                    Where A.Date between 20170101 and 20171231

                     

                    However, when I do this..  I get what I want.  I get all records on Doctor.  My date is a parameter. How do I make something like this work in Tableau. Blending does work, because it drops the record.

                     

                    Select Doctor.*

                    From Doctor

                    Left Join Table A

                      on Doctor.ID = A.DoctorID

                      AND A.Date between 20170101 and 20171231