4 Replies Latest reply on Nov 6, 2016 5:04 AM by John Croft

    Inner Join data from multiple worksheets using Date Column

    Mrunal Modi

      Hello,

       

      I am very confused with the concept of data blending and would like help here.

       

      Please see attached data sources using 3 worksheets from a single workbook -

       

      • VOL_UNPIVOT_SUMMARY
      • __cifs
      • __nfsv3

       

      The Date column contains hourly timestamp that are common and can be used to joins these worksheets.

      What I find hard here is joining the "VOL_UNPIVOT_SUMMARY" which contains mutiple rows for the same timestamp (hour).

       

      I am merely looking to overlay the protocol timeseries over the volume ones.

       

      Appreciate your help.

       

      Modi

       

        • 1. Re: Inner Join data from multiple worksheets using Date Column
          John Croft

          You'll need to save tableau workbooks as .TWBX packaged workbooks for the data source to remain embedded in the file. I'll pull the xlsx into the workbook. But You're not so much looking to blend. You just want to join the sheets on the data source tab. My experience is this is much different (and faster) than blending. I'll see what I can do with your files. But below is a screen shot where I joined several sheets in the same xlsx file.

           

          • 2. Re: Inner Join data from multiple worksheets using Date Column
            John Croft

            I think it was just a formatting issue with the different date fields in each sheet. Two were pulling into Tableau as 'string' variables. I just joined on date int he data source column. It showed an error but then I opened a new sheet and right click on each data type icon to the left of each date field highlighted below and set it to date time. Then I right clicked on the date variable names and set default properties date format to DD/MM/YYYY 00:00:00 military time (not using AM or PM). I'll upload the file but I'm not sure you can open it if your using Tableau 9. I can post it to my Tableau Public for you to download if you really need it. Just let me know.

             

            • 3. Re: Inner Join data from multiple worksheets using Date Column
              Mrunal Modi

              Thanks John,

               

              I have changed the Date variable type to "Date & Time" for both worksheets "__cifs" and "nfsv3" followed by using an inner join and average the data points in those worksheets instead of SUM?

               

              Do we need an inner join or left outer join?

              Also, If I have more rows per a certain timestamp in the "VOL_UNPIVOT_SUMMARY" worksheet then would I need to average the data points instead of SUM in the "__cifs" , "__nfsv3" and "__fcp" worksheets?

              I would have thought that I needed to avg the rows in  "VOL_UNPIVOT_SUMMARY" worksheet as it had more rows.

               

              Avg-instead-of-Sum.png

               

              Thanks mate

               

              Modi

              • 4. Re: Inner Join data from multiple worksheets using Date Column
                John Croft

                I typically left join. I think in your case the inner join and left join will do the same thing since all your sheets have connecting rows. Inner joins will filter out rows if the connecting id is not found in the base table.

                 

                I would sum the larger sheet (VOL_UNPIVOT_SUMMARY) and average the two smaller sheets since those rows will be duplicated when you join. I think you may be confusing what happens when one sheet has more rows. The smaller sheets are actually duplicated.