13 Replies Latest reply on Aug 7, 2013 5:45 AM by Charlotte Doherty

    custom SQL unions and join

    Charlotte Doherty

      Hi,

       

      I've recently discovered the custom SQL functionality in tableau and I've been using the UNION ALL code to great effect because I have a lot of Excel files with multiple tabs that need to be joined together.  (I know - I should be using a db, but that's just not possible right now).

       

      I've got the following code working perfectly, where each sheet contains identical columns just with data from a different month.  So the final table has columns Keywords, Rank, Date. 

      SELECT *

      FROM [Jan13$]

      UNION ALL

      SELECT *

      FROM [Feb13$]

      UNION ALL

      SELECT *

      FROM [Mar13$]

       

      Given united table:

      Keywords     Rank     Date

      kw1               1            01/01/2013

      kw1               1            15/02/2013

      kw1               1            31/03/2013

      kw2               2             10/01/2013

      etc, etc, etc.

       

      Now, I need to take the united data table and classify the keywords based on a lookup (in Excel speak) from another sheet KW Lookup which has columns: Keywords, Head, Agency


      Keywords     Head     Agency

      kw1               top          internal

      kw2               mid          external


      To give a final joined up table:


      Keywords      Rank          Date               Head          Agency

      kw1               1                 01/01/2013     top               internal

      kw1               1                 15/02/2013     top               internal

      kw1               1                 31/03/2013     top               internal

      kw2               2                 10/01/2013     mid               external

       

       

      Am I aiming for the impossible?  Please, any advice will be very much appreciated!

        • 1. Re: custom SQL unions and join
          Jonathan Drummey

          Hi Charlotte,

           

          Possibly the easiest solution would be to use Tableau data blending, where the KW Lookup sheet is brought in as a separate data source and blended on the Keywords field.

           

          Alternatively, you could wrap your initial union query and have that be a subquery that is used as the left side of a left join to the Keywords, here's an example:

           

          SELECT [u].[Keywords] AS [Keywords],

            ['KW Lookup$'].[Head] AS [Head],

            ['KW Lookup$'].[Agency] AS [Agency],

          FROM (SELECT [Jan13$].* FROM [Jan13$]

          UNION ALL SELECT [Feb13$].* FROM [Feb13$]) AS [u]

            LEFT JOIN ['KW Lookup$'] ON [u].[Keywords] = ['KW Lookup$'].[Keywords]

           

          I'll use a left join rather than an inner join because the left join is guaranteed to return all the results from the union query, if we used an inner join and there was a value for Keyewords that was not in the Keywords table then the inner join would not return those rows.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: custom SQL unions and join
            bha.jand

            Hi Charlotte,

             

            Another work around:

             

            If using custom SQL is not a compulsion for you (following your saying "because I have a lot of Excel files with multiple tabs that need to be joined together"), I would like to suggest you to consider using Multiple Tables(it is located where you select Custom SQL) while using Excel as one Source. When you consider this, each sheet of excel is considered as a table by Tableau(following your saying"based on a lookup (in Excel speak) from another sheet KW Lookup"), you can select the sheets only which you need. If the column names to be considered for join are same, like your Keywords in both sheets, Tableau will by default consider the join condition or you have an option to alter your join condition.

             

            As you have multiple tabs, you might try this option as well, as I hope it make things easy for you. I am sorry I could not quote your saying properly. I hope I am clear in explanation.

             

            Hope it helps.

            • 3. Re: custom SQL unions and join
              Charlotte Doherty

              This is great - thanks so much!

              • 4. Re: custom SQL unions and join
                Charlotte Doherty

                Hi Jonathan,

                 

                I'm having a problem with the code you kindly gave me - I keep getting this error message:

                 

                Database error 0x80004005: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

                 

                 

                Unable to connect to the Microsoft Excel file "C:\Users\CHARLO~1.DOH\AppData\Local\Temp\TableauTemp\1r7u3n711lvf3718232no0ipj5ne\Data\SEO Dashboards 2013\WMT rankings.xlsx". Check that you have access privileges for the requested file and that it is not open in another application.

                 

                This is the code I'm using - am I missing anything?

                 

                SELECT [u].[Keywords] AS [Keywords],

                    ['KW Lookup$'].[Head] AS [Head],

                    ['KW Lookup$'].[Agency] AS [Agency],

                FROM (SELECT * FROM [Jan13$]

                UNION ALL SELECT * FROM [Feb13$]

                UNION ALL SELECT * FROM [Mar13$]

                UNION ALL SELECT * FROM [Apr13$]

                UNION ALL SELECT * FROM [May13$]

                UNION ALL SELECT * FROM [Jun13$]

                UNION ALL SELECT * FROM [Jul13$]

                UNION ALL SELECT * FROM [Aug13$]

                UNION ALL SELECT * FROM [Sep13$]

                UNION ALL SELECT * FROM [Oct13$]

                UNION ALL SELECT * FROM [Nov13$]

                UNION ALL SELECT * FROM [Dec13$]

                UNION ALL SELECT * FROM [Jan14$]

                UNION ALL SELECT * FROM [Feb14$]

                UNION ALL SELECT * FROM [Mar14$]

                UNION ALL SELECT * FROM [Apr14$]

                UNION ALL SELECT * FROM [May14$]

                UNION ALL SELECT * FROM [Jun14$]

                UNION ALL SELECT * FROM [Jul14$]

                UNION ALL SELECT * FROM [Aug14$]

                UNION ALL SELECT * FROM [Sep14$]

                UNION ALL SELECT * FROM [Oct14$]

                UNION ALL SELECT * FROM [Nov14$]

                UNION ALL SELECT * FROM [Dec14$]) AS [u]

                    LEFT JOIN ['KW Lookup$'] ON [u].[Keywords] = ['KW Lookup$'].[Keywords]

                • 5. Re: custom SQL unions and join
                  Jonathan Drummey

                  Remove the comma on the third line after [Agency] and you should be all set.

                  2 of 2 people found this helpful
                  • 6. Re: custom SQL unions and join
                    Charlotte Doherty

                    Oops - good spot.  I've removed the comma but now I'm getting a new message (below).  I really appreciate your help on this!!

                     

                     

                    Database error 0x80040E10: No value given for one or more required parameters.

                     

                     

                    Unable to connect to the Microsoft Excel file "C:\Users\CHARLO~1.DOH\AppData\Local\Temp\TableauTemp\0783t6r14pgrdm1ft71kr0psnn6o\Data\SEO Dashboards 2013\WMT rankings.xlsx". Check that you have access privileges for the requested file and that it is not open in another application.

                    • 7. Re: custom SQL unions and join
                      kettan

                      I think you need to re-select your data source, see print screen below.

                      Tableau - Re-select Excel Workbook Connection.png

                       

                      Ps. This is an interesting article about the TableauTemp folder: Tableau Tip: If you can’t open a Tableau workbook because of the space on your hard drive, Tableau itself may very well be at fault.

                      3 of 3 people found this helpful
                      • 8. Re: custom SQL unions and join
                        Charlotte Doherty

                        Thank you so much Johan!!

                         

                        It works <does happy dance>

                         

                        Yay!!!!!!!!!!!!!!!!!!!!!

                         

                        Ahem.

                         

                        Thanks to you both Jonathan and Johan.  This is fantastic!

                        • 9. Re: custom SQL unions and join
                          kettan

                             It makes me joyful to know about your joy    Joy shared is double joy 

                          • 10. Re: custom SQL unions and join
                            Jonathan Drummey

                            This made me smile, thanks, Charlotte! (I do happy dances too, as well as impotently shaking my fist at JET SQL when it gives me nonsensical error messages).

                            • 11. Re: custom SQL unions and join
                              Charlotte Doherty

                              I know Jonathan is in high demand and very busy, so if anyone else can help me please shout

                               

                              I've found another use for having a union with a left join, but in my new example, my left join needs an AND statement.  I can't see anything wrong with my syntax below, but I'm getting the annoying error message "Database error 0x80040E10: No value given for one or more required parameters."

                               

                              I've checked my datasource, and confirmed all spelling, columns etc.  All are fine.  Please - if you know where I've gone wrong, please tell me

                               

                              SELECT

                                  [Data$].[Job Board] AS [Job Board],

                                  [Data$].[Order Date] AS [Order Date],

                                  [Data$].[Total Site Visits] AS [Total Site Visits],

                                  [Data$].[Visits to Product Pages] AS [Visits to Product Pages],

                                  [u].[Visits] AS [RHP Visits]

                               

                              FROM (SELECT *, 'Totaljobs' AS [Job Board] FROM [TJ$]

                              UNION ALL SELECT *, 'Careerstructure' AS [Job Board] FROM [CS$]

                              UNION ALL SELECT *, 'Caterer' AS [Job Board] FROM [CAT$]

                              UNION ALL SELECT *, 'CWJobs' AS [Job Board] FROM [CW$]

                              UNION ALL SELECT *, 'Retailchoice' AS [Job Board] FROM [RC$]

                              UNION ALL SELECT *, 'Salestarget' AS [Job Board] FROM [ST$]) AS [u]

                               

                                  LEFT JOIN [Data$]

                                  ON [Data$].[Order Date] = [u].[Order Date]

                                  AND [Data$].[Job Board] = [u].[Job Board]

                              • 12. Re: custom SQL unions and join
                                Jonathan Drummey

                                Hi Charlotte,

                                 

                                Nothing is obviously jumping out at me. Does the left join work (i.e. return results) with a single clause? I'd try each of the two clauses separately to check. If they work separately, then they should be able to work together, it's just a matter of figuring out the magic symbols - I mean syntax. If the single clause join doesn't work, then I'd decompose the original query and the join into two separate queries and get those to work, then go back to trying to join them. For example, I've been tripped up UNION'ing Excel worksheets together by one worksheet having a space at the end of the sheetname that I didn't see, i.e. "Jan", "Feb", "Mar " that blew up the inner SELECT with the same error you got.

                                 

                                Jonathan


                                • 13. Re: custom SQL unions and join
                                  Charlotte Doherty

                                  Thanks for checking it over Jonathan, it's good to know I'm missing any pesky commas   I will follow your advice and when I find the problem I'll post back about it.

                                  I think I'm going to need some coffee first!

                                   

                                  Charlotte