4 Replies Latest reply on Apr 10, 2014 12:59 PM by Chuck Mobley

    Custom SQL to make code for deduping and OUTER JOIN work together

    Stephen Stark

      Hello all, I am trying to connect to an Excel file which contains 2 tables using custom SQL, while achieving 2 objectives: I need to dedupe one of the tables (I've got this code down) and full outer join the two tables (which I also have the code for). Does anyone know what syntax to use to make these separate commands work in conjunction with one another? Thank you!

       

      Steve

        • 1. Re: Custom SQL to make code for deduping and OUTER JOIN work together
          Chuck Mobley


          Hi, Steve -

           

          First: Understand that the JET database engine is not able to use all of its 'given' functions if you try to run conventional SQL queries against, say, an Excel file.

           

          The cure? *Extract* your Excel data into 2 .tde files. That allows the JET database engine to use all of its powers.

           

          Now...  back to your question:

           

          You can probably use something like this (against the data extract):

           

          SELECT *

          FROM

          (

          SELECT *

          FROM

          <table1>

          INNER JOIN

          <table1>

          ON

          <table1>.<table1_key_field>

          -- You have now de-duped <table1>.

          ) AS DEDUPED_TABLE_1   -- (You might need to remove the word "AS" to get it to run correctly.)

          FULL OUTER JOIN

          <table2>

          ON

          DEDUPED_TABLE_1.<table1_key_field> = <table2>.<table1_foreign_key_field>
          ------------------------------------------------------

           

          I have not tried this against actual data - I just jotted this down quickly, between tasks at work. Good luck.

           

          Chuck M.

          visualReference LLC

          • 2. Re: Custom SQL to make code for deduping and OUTER JOIN work together
            Chuck Mobley

            Hi, Steve -

             

            I tried my previous post's proposed solution, but found that it did not work - so, here's a different approach.

             

            If you have MS Access available, import your two Excel worksheets into two Access tables. Next, use the previously mentioned SQL query to create a third 'output' table from your two 'input' tables.

            SELECT *

            FROM

            (

            SELECT *

            FROM

            <table1>

            INNER JOIN

            <table1>

            ON

            <table1>.<table1_key_field>

            -- You have now de-duped <table1>.

            ) AS DEDUPED_TABLE_1   -- (You might need to remove the word "AS" to get it to run correctly.)

            FULL OUTER JOIN

            <table2>

            ON

            DEDUPED_TABLE_1.<table1_key_field> = <table2>.<table1_foreign_key_field>

             

            As I mentioned, make a new, third table with the results of this query, and use that MS Access table as your data source in Tableau. Then, extract that Access table into a Tableau data extract... and you'll be off to the races...

             

            Yes, you could create a Tableau view from the 'live' Access table, but I am partial to Extracts.  :-)

             

             

            Chuck M.

            visualReference LLC

            1 of 1 people found this helpful
            • 3. Re: Custom SQL to make code for deduping and OUTER JOIN work together
              Stephen Stark

              Hi Chuck,

               

              Thanks so much for taking the time to put together this solution- I'll check it out! (Sorry it took so long for me to respond) So essentially the functions I am trying to perform in conjunction with one another fall within the category of functions that don't work using conventional SQL queries in Tableau? Again, thanks a ton.

               

              Steve