4 Replies Latest reply on Nov 29, 2017 9:10 AM 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
          venkatram

          I'm assuming this will achieving using custom SQL.

           

           

          Connect to a Custom SQL Query

           

          select * from fact_metric A,Dim_trade B

          inner join B

          on

          A.Trade_key=B.tdrade_key

          and

          (A.dataset_key=b.entered_by_dataset or b.entry_by_dataset=1)

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

            Thank you for your reply Venkat. That is the simplest answer I have to this problem. Unfortunately, I cannot use custom query as there are over 15 dimension tables joining one fact table. I have simply shown the table in question in the image.

             

            My question is, is there any way that Tableau can allow me to do the join that is this complex?

            • 3. Re: Table joining on multiple conditions
              venkatram

              Hey Amit,

               

              Does't matter how many table you have to use. You can do all tables join with Fact_table like below

               

               

              select * from fact_metric A,Dim_trade B,Table C,Table D, Table E

              inner join B

               

               

              on

              A.Trade_key=B.tdrade_key

              and

              (A.dataset_key=b.entered_by_dataset or b.entry_by_dataset=1)

               

               

              inner join on table C

              on B.id=C.id

              inner join D

              on B.id=D.id

              inner join E

              on b.id=e.id

               

               

              Etc---- you can join N number of tables with Fact table like above.

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

                Hi Amit,

                 

                I answered this same question at Re: Table joining on multiple conditions. In the future please don't ask the same question multiple times. The reason is that pretty much everyone answering the forums is a volunteer and asking the question multiple times causes unecessary duplication of effort among the volunteers, thanks!

                 

                Also one more comment on the other responses on this thread: While yes, we can write Custom SQL that will include many tables and have as complex a join criteria as our database supports if we're using a live connection we're also setting ourselves up for performance issues. The reason why is that Tableau always uses the entire block of custom SQL...even if we're just querying for a dimension with a few values Tableau will run the entire custom SQL. In a complex view or dashboard where multiple queries are issued this can get very slow. Depending on the need there are various workarounds...the best reference on improving Tableau performance is the Designing Efficient Workbooks white paper by Alan Eldridge http://www.tableau.com/learn/whitepapers/designing-efficient-workbooks.

                 

                Jonathan