4 Replies Latest reply on Jun 29, 2018 1:39 PM by Scott Schmeling

    Data from multiple data sources with a pivot

    Scott Schmeling

      Hi,

       

      I'm wondering if someone can tell me the best way to join the data from multiple data sources that don't match?

       

      In one data source I have:  the following information:

       

      Data Source 1: Internal Referrals

      Client ID
      Client Record #Internal Referral DAteEnrollment DateActivation DateLatest Visit Date
      Graduation DateClass AttendedDays to EnrollSpecial DateDrop OUt Date

       

      Data Source 2: Opportunities

      Client IDClient Record #Discharged DateDischarged Location

       

      Data Source 3: External Referrals

      Client IDClient Record #External Referral DateReferred to Location

       

       

      I need to take all that data and make a table that looks like this:

       

      Header 1Header 2
      OpportunitiesCountD(ClientID)
      External ReferralsCountD(ClientID)
      Internal ReferralsCountD(ClientID)
      EnrolledCountD(ClientID)
      ActiveCountD(ClientID)
      GraduatedCountD(ClientID)
      Drop OutsCountD(ClientID)

       

       

      The main issue I am running into is that I have to Pivot the Internal Reference DAta as I am bringing it into Tableau to make a it a Category Column and a Date Column.

      I have created a calculated column on Opportunities and External Referrals Called Category with a name in it. 

       

      But am not sure how to now line the three up in one table.  Any help would be greatly appreciated.  Pointing me to some ideas on where to look for the answer would be great- I understand you can't work on it without the book, so just pointing me in the correct direction is probably the best.

       

      I tried Union but can't seem to get it to work, I tried creating groups through blending but again not able to get it to work very well.

       

      Thanks,

        • 1. Re: Data from multiple data sources with a pivot
          Jim Dehner

          Hi Scott

          This is going to tough in Tableau but in Tableau prep it would be easier -

          you would be able to join or union after you pivot the first file see the link

           

          Tableau Prep

           

          if you have some dummy data for the 3 files please attached it and I will give it a try

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Data from multiple data sources with a pivot
            Scott Schmeling

            Hi Jim,

             

            That is the conclusion I was quickly coming to.  I figured I might try a IfNull Statement to get in the general area.  I will try to upload a dummy workbook, it is difficult as I work in Healthcare and very difficult to upload anything.  Give me some time and I will create one at home and upload it from there.

             

            Also- Tableau Prep is out.  I tried asking for it at least to try it out and the response was no- we don't want another tool.  Plus we don't want a tool that only you know how to use.  I also asked for data to be prepivoted in SQL but they don't understand why I need that.  The Director is new to this and she is not understanding the differences in data very well in this case.  She doesn't understand the concept of Tall versus Wide, "we have always used Wide in the past". 

             

            Right now going to resort to manual in Excel to see what I can do to at least get the data into a usable format for me.

             

            Thanks,

            • 3. Re: Data from multiple data sources with a pivot
              Jim Dehner

              I hear the pain

              There are other data shaping tools if you have access to those in lieu of Tableau Prep

              Jim

              • 4. Re: Data from multiple data sources with a pivot
                Scott Schmeling

                Any suggestions?  I don't know too many - maybe SAS which we have but I don't know how to program as well with R.  Something fairly easy to learn would be great.