3 Replies Latest reply on Nov 13, 2015 11:51 AM by pooja.gandhi

    Joining two worksheets and padding for null values

    Shaun Paterson

      Hi folks,

       

      Sorry to ask a question that has been asked multiple times before in various different manners. But I've been wracking my brains for days and I'm not getting anywhere.

       

      I have an Excel Workbook with 2 work sheets. Worksheet SITES has a list of static sites. Worksheet AVAIL records details of when the site has been unavailable in minutes during a month.

       

      What I need is the combination of both Worksheets including all possible Null Values. This is because if the data is missing or null, then we know that the availability is 100%. It's easier to look at an example.

       

      Joining problem.png

      I'm sure I should use Custom SQL to do this but I'm hopeless at it. If anyone could suggest a way forward, I'd be very grateful.

       

      I haven't attached a Tableau TWBX file, because I have nothing to show. I've added the Excel file, with the same information as shown in the example screenshot above.

       

      Thanks so much for your help... Shaun

        • 1. Re: Joining two worksheets and padding for null values
          pooja.gandhi

          Shaun,

           

          Can't you just JOIN the SITES and AVAIL tabs on SITE ID in the excel workbook in the data source window? I see 36 rows of data in your expected results and 36 rows of data in the resulted JOIN. You can do an INNER JOIN if site IDs are always present in the AVAIL tab, if not you can do a LEFT to account for all sites present in SITE tab regardless of their presence in the AVAIL tab.

           

          Capture.PNG

          Or are you trying to do something else that I am misunderstanding?

          • 2. Re: Joining two worksheets and padding for null values
            Shaun Paterson

            Hi Pooja,

             

            Can you check again please? I think, in your example you have linked the SITES and RESULTS tabs, rather than the SITES and AVAIL tabs.

             

            Many thanks for helping.

             

            Cheers... Shaun

            • 3. Re: Joining two worksheets and padding for null values
              pooja.gandhi

              Hi Shaun,

               

              Ok, that was so silly of me. I can't believe I overlooked that. You are not going to be able to do what you currently have.

              Since the JOIN is on site ID, if that site ID doesn't have available data in the AVAIL tab you are going to get 1 null row for that site ID (not 6 null rows for each date or 12 null rows for each type 2G or 3G).

               

              Capture.PNG

               

               

               

              Tableau doesn't know to add different rows for different months it wasn't available. Since there are 3 sites and 6 dates and 2 types (2G and 3G), your results needed is 36 rows regardless of data being available or not. But with the way you have things set up right now, I do not think you are going to get that.

               

              For the rows to appear for each month, you are actually going to need the month and the types available in the SITE tab as well. And the AVAIL tab to reflect totals for sites that were available. That way all the sites that were unavailable for each type, you will end up getting a null row.

               

              How are you getting the data in excel? If its an output result from a SQL query or the like, you might want to look at how you can change that so each month is reflective in the expected results you need.

               

              Hope this helps!

               

              Pooja.