12 Replies Latest reply on Mar 24, 2016 10:14 AM by cor.bader

    create a union of two sheets in the same Excel workbook in Tableau 8.2?

    rafael diaz

      Hello,

      I've been searching around trying to find out how I can create a union of two sheets from the same Excel workbook in Tableau 8.2 and I found the following article on how to do exactly that, but at step 2 doesn't exist for version 8.2 unless I'm missing something. I'm new to Tableau and I'm trying to learn it on the fly as I create dashboards for a customer we are working with.

       

      I have an Excel file that contains sheets whose columns and rows are identical except that each sheet is for a different year. Of course the contents are different. I want to blend the data across the years to show trends and do predictions.

       

      Was the custom SQL option removed in Tableau 8.2 in the data connection dialog box?

       

      From the KB article dated June 19, 2014:

      Step 1

      Open a new workbook, click Connect to Data > Microsoft Excel, and then find and select your Excel workbook.

      Step 2

      In the Excel Workbook Connection dialog box, select Custom SQL.

      Step 3

      Replace the default query with a UNION ALL query similar to the one shown below, using your worksheet names.

      In this example, the worksheets do not include a region field, because each sheet represents a region. So in addition to the union, you create a column for Region in the Tableau view, plugging in the worksheet name as the value.

      --------------------------------

       

      Raf

        • 1. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
          Shawn Wallwork

          To quote Richard Leeke, it's "cunningly hidden". That's a typical British understatement. Here's what you're looking for:

           

          Hidden.png

          That will then get you the New Custom SQL option.

           

          Cheers,

           

          --Shawn

          • 2. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
            rafael diaz

            Ok I tried using that option but it's not bringing up the Excel workbook connection dialog box. Here's what I get:

             

            When I click on the New Custom SQL circled above, I get that dialog box.

             

            The Dialog box I'm looking for is as follows (from the KB article Analyzing Related Data on Multiple Excel Tabs | Tableau Software)

            ---------------------------

            Step 2

            In the Excel Workbook Connection dialog box, select Custom SQL.

            Step 3

            Replace the default query with a UNION ALL query similar to the one shown below, using your worksheet names.

            In this example, the worksheets do not include a region field, because each sheet represents a region. So in addition to the union, you create a column for Region in the Tableau view, plugging in the worksheet name as the value.

            SELECT *, 'East' AS [Region]
            FROM [East$]
            UNION ALL
            SELECT *, 'West' AS [Region]
            FROM [West$]

            Notes

            • If your worksheets do include a Region column, you can simply use SELECT * FROM [SheetName$].
            • If your worksheet names have spaces in them, you need to include single quotation marks around the name in your custom SQL query. For example, for the sheet name Eastern Region, you would type ['Eastern Region$'] as part of the query.

            ------------------------------------

            Raf

            • 3. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
              rafael diaz

              All,

               

              I checked a couple of other threads on the forums on this issue and based on the following thread, this just isn't possible with the new 8.2 Windows version of Tableau. Apparently this option was removed due to the complexity of implementing it. It is available in 8.1 however.

               

              See Re: Hello, in version 8.2, where is the custom sql option in the excel workbook connection ?

               

              Also the online documentation says this as well - sort of.

              -------

              Connecting to a Custom SQL Query

              For most relational data sources you can connect to a specific query rather than the entire data source. Often this can be useful when you know exactly the information you need and you understand how to write SQL queries.

              Note: For Microsoft Excel and text file data sources, this option is available only when using the legacy connection or in workbooks that were created before Tableau Desktop 8.2.

              --------

              • 4. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
                Shawn Wallwork

                Rafael, you are drawing some false conclusions. This can be done. Not as easily as in 8.1, but it can be done. So your answer is incorrect and will mislead others, so please take it down. Here are the step to create a UNION ALL in 8.2:

                 

                First open the Excel file using the legacy connection like I showed you above:

                 

                SQL-1.png

                Second, before you drag anything else into the connection box, double-click the New Custom SQL icon to open up the blank box, and type in your union, something similar to this:

                 

                SQL-3.png

                 

                This is a Superstore example and is a union of the orders table onto itself. In your case if the orders and return tables were exactly the same column-wise then the union would be something like this:

                 

                SELECT *

                    , 'Alpha' AS [Type]

                FROM [Orders$]

                 

                UNION ALL

                 

                SELECT *

                    , 'Beta' AS [Type]

                FROM [Returns$]

                 

                Like I mentioned, in 8.1 this was easier because you could get Tableau to write the first half of the union for you, then it was easy to figure out the syntax for the second (and third) parts. The main rules to get Excel file unions to work in 8.2 are:

                1. Use the legacy connection to open the Excel workbook
                2. All tables in a union must be in the same Excel workbook
                3. Use New Custom SQL to create the union
                4. The syntax for referring to the worksheets is [WorksheetName$]
                5. All tables to be union must have the same number of columns and names must be same

                 

                Good luck,

                 

                --Shawn

                • 5. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
                  rafael diaz

                  Hi Shawn,

                  Thanks for clarifying this. That empty "Edit Custom Query" dialog box threw me for a loop since it didn't match what the KB article had but now I realize that the KB article was using version 8.1. Your solution worked great!

                   

                  Many Thanks!

                  1 of 1 people found this helpful
                  • 6. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
                    Doug Shea

                    shawnwallwork wrote:

                     

                    Rafael, you are drawing some false conclusions. This can be done. Not as easily as in 8.1, but it can be done. So your answer is incorrect and will mislead others, so please take it down. Here are the step to create a UNION ALL in 8.2:

                     

                    Footnote: Unless I'm mistaken, there is no legacy connector on Macs, where 8.1 never existed in the first place. As far as I can tell, it is not possible on a Mac to UNION ALL two sheets from an Excel file. There's is no dropdown on the Open button on a Mac.

                     

                    Please correct me if I'm mistaken.

                    • 7. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
                      Jonathan Drummey

                      Hi Doug,

                       

                      The "legacy" connector is Microsoft JET, which does not exist for the Mac, and that's how Tableau implemented Custom SQL for Access, Excel, & text sources. The new (Tableau built) connector doesn't have Custom SQL as an option. So some Mac UNION options are:

                       

                      - Install MySQL, PostgreSQL, or some other database server on your Mac.

                      - Do something fancy in Excel with named ranges, the Consolidate function, etc.

                      - Use a scaffold source worksheet that has all the dimensional values you need for a Tableau data blend, then use the scaffold as a primary and your other sources as secondary, then calculated fields in Tableau to generate the "union", because you'll have duplicate fields.

                      - Create a scaffold source worksheet has a "Source" column with two values (1 and 2), then add a new Source column to each data worksheet, the first gets 1 for a value for every row and the second gets a 2. Create a data source in Tableau that left-joins the two data worksheets to the source worksheet, then calculated fields in Tableau to generate the union, because you'll have duplicate fields.

                       

                      Jonathan

                      • 8. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
                        Samantha Weeks

                        Since I don't want to add any columns, my union looks like this:

                         

                        SELECT * FROM [Jan$]

                        UNION ALL

                        SELECT * FROM [Feb$]

                        UNION ALL

                         

                        and so on whereas each tab is labeled as a different month.

                         

                        I get an incomplete query message. I don't have any sheets in the box. I am using the New Custom SQL box.

                         

                        Any help is appreciated!

                        • 9. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
                          Samantha Weeks

                          I don't see how/where that is done in the previous responses. I thought it was given what workbook I am in because it's listed under "Workbook".

                          • 10. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
                            Samantha Weeks

                            I'm using 9.0 and getting a message that your workbook was created by a newer version and to contact admin.

                             

                            At any rate, I cannot get the first two tabs within the worksheet to join; Tab 1 = Jan and Tab 2 = Feb.

                             

                            I've edited what I had to mirror yours more closely.

                             

                            SELECT *
                            ,'Jan' AS Month
                            FROM [Jan$]
                            UNION ALL

                            SELECT *
                            ,'Feb' AS Month
                            FROM [Feb$]
                            UNION ALL

                            • 11. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
                              Samantha Weeks

                              That helped but also, I could get it to work by only doing union but not union all. Either way, I have unique data on each tab so this works for me. Thank you for your help!

                              • 12. Re: create a union of two sheets in the same Excel workbook in Tableau 8.2?
                                cor.bader

                                Just released!  Unions now native in 9.3 for Excel and text files.

                                 

                                More information here: Tableau 9.3: Data, set, flow