6 Replies Latest reply on Dec 8, 2017 12:01 PM by Jonathan Drummey

    Table joining on multiple conditions

    Amit K

      Hello,

      I am on Tableau V 10.3 Desktop.

      I am trying to join two tables in my data source.

      1) Table 1: fact_metric

      2) Table 2: dim_trade

       

      The two tables need to be joined based on the following condition.

       

      fact_metrc.trade_key = dim_trade.trade_key

      AND

      (fact_metric.dataset_key = dim_trade.entered_by_dataset_key OR dim_trade.entered_by_dataset_key =1)

       

      The first part is okay. I joined based on the trade_key but how do I get the additional OR condition in the two statements? I am trying the 'Edit Join Calculation' too. but that appears only on one side of the equation also nowhere do I see the option to add 'AND' 'OR' conditions. Am I missing here something? Can someone please guide me to the correct kb if there is one on this topic?

       

      Appreciate your help. Thanks a lot!

        • 1. Re: Table joining on multiple conditions
          Jonathan Drummey

          Two questions:

           

          1) Are both tables in the same data source or different data sources?

          2) What is/are your data source(s)? e.g. SQL Server, Oracle, …?

           

          Jonathan

          • 2. Re: Table joining on multiple conditions
            Jonathan Drummey

            And two more questions upon further reflection:

             

            3) Are you using a live connection or a Tableau data extract?

            4) What version of Tableau are you using? (10.1, 10.2, 10.3, etc.?)

             

            There might be an in-Tableau way to do this, this is the kind of thing where I'd generally use a custom view in the data source rather than trying to build the logic in Tableau.

             

            Jonathan

            • 3. Re: Table joining on multiple conditions
              Amit K

              Thank you Jonathan for your replies. Here are the answers.

               

              1) Are both tables in the same data source or different data sources? - Same Data source. There are total 19 dimension tables that are joining one fact table. To reduce clutter, I simply removed other tables from the image.

              2) What is/are your data source(s)? e.g. SQL Server, Oracle, …? - The database is HP Vertica (I will get the version from the DBA guys if needed. Right now they are offline.   )

              3) Are you using a live connection or a Tableau data extract? - This is a live connection.

              4) What version of Tableau are you using? (10.1, 10.2, 10.3, etc.?) - Tableau 10.3 Desktop

               

               

              this is the kind of thing where I'd generally use a custom view in the data source rather than trying to build the logic in Tableau.

              Yes, custom sql query was my first thought but as there are 19 tables joining one fact table, custom query is really not an option for me. This data source is a single data source made available to all traders. They use this one to build their own reports.

              So instead of custom SQL, I wanted to do the complex join.

              • 4. Re: Table joining on multiple conditions
                Jonathan Drummey

                Thanks for the answers, I think I have a workable solution for you.

                 

                re: custom view, just to be clear, I wasn't suggesting custom SQL in Tableau, instead building a view using the CREATE VIEW in the data source itself which could potentially run faster than custom SQL.

                 

                In any case, we can get the results you want using a join and a data source filter. Data source filters are added to the WHERE clause of every query so the SQL syntax will look something like the following:

                 

                SELECT ...fields here...

                FROM fact_metric

                INNER JOIN dim_trade on fact_metric.trade_key = dim_trade.trade_key

                WHERE (fact_metric.entered_by_dataset_key = dim_trade.entered_by_dataset_key OR dim_trade.entered_by_dataset_key = 1)

                 

                In many databases the query plan for a query using this kind of WHERE clause should resolve to something very close to the original join condition you specified (I don't know enough about Vertica to be sure).

                 

                I set up a sample data set with a row in each table that shouldn't come through the join & where conditions, and created a calculated field to be ultimately used as a data source filter. Here's the workout source & view:

                 

                Screen Shot 2017-11-29 at 11.54.52 AM.png

                 

                 

                Then I duplicated the source and added a data source filter where the Data Source Filter field is True and the view looks like this:

                 

                Screen Shot 2017-11-29 at 12.02.29 PM.png

                 

                v10.3 workbook is attached, let me know if you have any questions!

                 

                Jonathan

                • 5. Re: Table joining on multiple conditions
                  Amit K

                  Thank you for your suggestion Jonathan. I will try out the solution. Unfortunately, I am on Tableau Desktop 10.3 so cannot open your workbook as I get the error
                  "This file was created by a newer version of Tableau. Please contact Tableau Software to upgrade your version."

                   

                  But I will try out the solution and get back soon.

                  • 6. Re: Table joining on multiple conditions
                    Jonathan Drummey

                    That's strange because I created this on 10.3 and the version information in the file is all 10.3 settings.

                     

                    Here's a 10.2 version.