4 Replies Latest reply on Sep 26, 2013 5:12 PM by Lori Johnson

    How can I join/blend/extract etc. these two files?

    Lori Johnson

      Hello -

      Attached are two mini excel files I created to simplify the learning. One is the
      February report showing the number of books a group of students read. And the
      other is the same report for March. How can I join/blend/extract etc. these two
      files to create a “report for February & March” without copying and pasting
      the columns and rows. Suppose that these excel files contain 50K+ rows and
      many, many columns and copy/paste is not a good option.

      My second question is:  if I created an awesome viz and a dashboard with the
      February data , how can I use the extract data (refresh) option to transfer my
      Feb report into the March report?  I tried many times based on training
      instructions and failed. Can you provide a few screenshots?

      I appreciate your help!

       

        • 1. Re: How can I join/blend/extract etc. these two files?
          Matt Morgenroth

          the answer is fairly simple so long as you take the correct approach.

          answer 1 (don't do this):

          these two data sets are candidates for a UNION join

          a UNION join is essentially stacking tables one on top of the other where the columns align and data types in the aligned columns are similar across the two tables.

          more here http://en.wikipedia.org/wiki/Set_operations_%28SQL%29

          This post has how to do and also why you shouldn't   UNION of two sheets in the same Excel workbook

          I've also answered why you shouldn't below...answer 2 is best

           

          answer 2 (do this):

          you could also make an extract of february

          connect to the xls file then go to File > Data > Data connection name > Extract

          once the extract is complete, right click the connection > Extract > Add Data From File

          See attached image for reference

          This is the better option because the extract engine is very fast, this become important when # of rows gets above 100Ks

          Also the UNION performed in a custom SQL connection is evaluated with each change made to the dashboard, effectively with each query sent to excel to satisfy your questions from changing the viz.  This is slow.

           

          What if your dates are formatted differently between the February and March sheets or other such data anomalies?

          No easy fix, data types will need to match.

          1 of 1 people found this helpful
          • 2. Re: Re: How can I join/blend/extract etc. these two files?
            Lori Johnson

            Thank you, Matt. But what am I doing wrong?

             

            “you could also make an extract of february Great!

            connect to the xls file then go to File > Data > Data connection name > Extract      I don’t have File > Data > Data Connection (see attached file)

            once the extract is complete, right click the connection > Extract > Add Data From File 

            See attached image for reference”  Sorry, where is the attached image?

             

             

             

            • 3. Re: How can I join/blend/extract etc. these two files?
              Matt Morgenroth

              Sorry for the confusion Lori.  In your doc I see you wound up with two data connections.

              With adding data from file to an existing extract you end up with all data in one connection.

              This video should clear it up:

              2013-09-26_1859 - mattgreenlake's library

              • 4. Re: Re: How can I join/blend/extract etc. these two files?
                Lori Johnson

                Matt, thank you!

                 

                You are awesome!

                 

                This is very, very helpful!

                 

                And this is the answer to my first question.

                 

                Actually, my second question was more about how to transform a February viz to a March viz by using extract data option? My expected result is similar to the refill we get by using the 'replace data'  option. But I need to use data - extract -refresh- method. Basically, I need to apply the attached instructions. I tried and tried; it didn't work.

                Any suggestions?