4 Replies Latest reply on Jan 30, 2019 12:45 AM by Mahfooj Khan

    Help on Left Join

    Karthikeyan A R
    Karthikeyan A R Jan 29, 2019 4:28 PM

      Hi all,

       

      I am not sure whether I am missing something basic here. I am trying to implement the join in Tableau as shown below,

       

      Query A:

      Select * from Table A

      Left Join Table B

      on A.Key=B.Key

      and B.YEAR in (2012, 2013)

       

      But what I can configure in Tableau is as shown below through data model level filters and UI filters.

       

      Query B:

      Select * from Table A

      Left Join Table B

      on A.Key=B.Key

      where B.YEAR in (2012, 2013)

       

      Query A and B directly in the database gives different results and I would like to implement Query A in Tableau. I don't want to go with custom SQL option as the dashboard is already developed with a lot of derived dimensions and measures which I think will go off If I use a custom query.

       

      Can anyone please advise me on how to configure the data model in Tableau Desktop same as Query A?

       

      Thanks in advance!

       

      Warm Regards,

      Karthikeyan.

      • 79 Views
      • Categories: Data Sources & Connections
      • Tags:
        • 1. Re: Help on Left Join
          Ken Flerlage
          Ken Flerlage Jan 29, 2019 5:47 PM (in response to Karthikeyan A R)

          I'm confused. Query A and B are exactly the same...so it's impossible to tell what is wrong with query B. Can you please share some screenshots of the data pane and your filters, etc. so we can see what you've done?

          • 2. Re: Help on Left Join
            Mahfooj Khan
            Mahfooj Khan Jan 29, 2019 8:20 PM (in response to Karthikeyan A R)

            Hi,

             

            Yeah it will return different result sets because

             

            Query B

            In this example, you've used the LEFT JOIN clause to query data from the A and B tables. The query returns keys and its detail, if any, for the year 2012/13.

             

            Query A

            However, if you move the condition from the WHERE clause to the ON clause. It will have a different meaning. In this case, the query returns all keys but only for years 2012/13 will have detail associated with it.

             

            Notice that for INNER JOIN clause, the condition in the ON clause is equivalent to the condition in the WHERE clause.

             

            Hope this help.

             

            Mahfooj

            • 3. Re: Help on Left Join
              Karthikeyan A R
              Karthikeyan A R Jan 29, 2019 10:15 PM (in response to Mahfooj Khan)

              Thanks,

               

              Is there any way to implement the Query A setup in Tableau data model? Basically filter restricted to child table (table B).

               

              Warm Regards,

              Karthikeyan.

              • 4. Re: Help on Left Join
                Mahfooj Khan
                Mahfooj Khan Jan 30, 2019 12:45 AM (in response to Karthikeyan A R)

                Hi,

                 

                Why don't you create a system view out of Query A in your back end database. Then connect that view in tableau for your analysis.

                If you wants to implement it in tableau then first join both source (A and B) with [Key] field. Then put a data source filter on [Year] field from source B and select only 2012 and 2013. I believe in that you way you can have your set of data which your query A is resulting.

                 

                How to join and put data source filter you may follow these knowledge base articles.

                Join Your Data - Tableau

                Filter Data from Your Data Source - Tableau

                 

                Let us know if this help.

                 

                Mahfooj