6 Replies Latest reply on Apr 19, 2018 3:49 PM by mike.czyzewski

    Data connection/structure guidance question

    mike.czyzewski

      Hi!

       

      Noobie here, thanks in advance for any guidance and advice!

       

      I've been struggling with how to build a visualization and I've come to terms that my first challenge is getting the data to talk to each other.

       

      The end visualization I need is a cross tab style report where management can select a name and various metrics appear.  See here, I've color coded data sources/calculations:

       

      screen shot for forums.JPG

       

       

      Raw data is from multiple Excel based file exports.  As seen above, there is a gift export, an activities export etc.  My first assumption is that, in order to build the view above, the data needs to talk to each other.  I don't think I can do a join/blend or a union, but I could be wrong about this.  Because I am doing some data transformation in Excel, I figure it makes sense to take my various sheets and combine in Excel before I bring it to Tableau.

       

      A common field between the various exports is solicitor name, so I was thinking about building an Excel sheet where the various imports are on one sheet and linked together with a formula field which pulls solicitor name.  I think I would be able to filter on this column in Tableau and return Joe Smith's dollars raised, visits etc. which I would need for my visualization.

       

      MOCKUP of data structure.JPG

       

      Again, noobie here, so are there any pitfalls to the way I am approaching this?  Are there better solutions to my problem?

       

      Thank you!

        • 1. Re: Data connection/structure guidance question
          Jim Dehner

          First welcome to Tableau ans second thanks for asking about data structure before creating your data file

           

          Here is something to think about - Tableau and other database driven systems are fundamentally different from excel (which is cell driven)

          Tableau works on dimensions  (think like columns in excel) and the values in those dimensions (measures)

          If the data elements in your 3 tables are the same the (the sheets structure is identical - the difference is the segment) then I would suggest you make the 3 files separate sheets in the same excel workbook.  Then when you bring them into Tableau you can union them together (think like appending data in excel or access)

           

          if the data elements are different the you would join the sheets together based on the solicitor name

           

          in any event you would not want to do what is shown in the visual above - if you think like excel all the White Space will be loaded a s NUlls and you will end up having to deal with them in every sheet - also you will have a wide - short data set - Tableau works best with Tall narrow data sets

           

          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.

          • 2. Re: Data connection/structure guidance question
            mike.czyzewski

            Hi Jim!

             

            Thanks for replying!

             

            Sheet structure is different, so a union will not work.

             

            Due to updated information, I only need to use two of the exports, so that simplifies life a bit.

             

            I've attached a cleaned up twbx so you can see the data.

             

            I built a join in Tableau.  On the Action Data sheet, I am using the "Assigned to - Single Name" column and, in the Opportunity Data sheet, I am using the "Opportunity assigned to - Single Name" column.  I looked at "Amount funded data" from the opportunity sheet as a checksum.  It was considerably larger than what I expected.

             

            Any thoughts?

             

            Thank you again for your time!

            • 3. Re: Data connection/structure guidance question
              Jim Dehner

              Hi Mike

               

              The fields that you joined the data on are not unique - and the records are duplicating as the join is completed -

              in the one file you have what looks like a unique field - Opportunity Name - is it truly unique and is there something in your other file that is comparable?

               

              Jim

              • 4. Re: Data connection/structure guidance question
                mike.czyzewski

                Jim,

                 

                I think I've done it.  See attached twbx.  I changed up the data a bit and kept the system record ID column for both of my worksheets. 

                 

                Because the system record IDs are unique and do not share values between the two data sheets, I did a full outer join and checked the amount funded sum.  It looks good!

                 

                Am I missing anything here?

                 

                Thanks!

                • 5. Re: Data connection/structure guidance question
                  Jim Dehner

                  Guess you ar there

                  Glad to help out

                  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.

                  • 6. Re: Data connection/structure guidance question
                    mike.czyzewski

                    I guess so!  As I go through the tableau build, I'll be double checking numbers to ensure that nothing is fishy.

                     

                    Thank you SO MUCH for talking me through this!  I could not have done this without you.

                     

                    Cheers!