13 Replies Latest reply on Aug 5, 2016 10:53 AM by Santiago Perez

    Multiple-Join Clauses

    Santiago Perez

      Tableau desktop allows you to define multiple join clauses . Note that I'm not simply talking about joining multiple tables, I'm talking about having more than one right join coming out of a single table.

      I have scoured the web and have found no documentation for this feature. When I've tried to use it, I get this error

      error.PNG

       

      my key tables are called Fund, Investment and Account. A fund can invest directly in Accounts or indirectly by investing in a fund that then invests in accounts. So the chain would be:

      fund->Investment----------------------------------->

                                  ->Fund_2->Investment_2->

                                                                               Account

      With two join clauses on Investment depending on whether it was direct or indirect. Is there any documentation on this feature available, and how can I achieve the above result? Can't post a twbx as the issue is with the data connection

        • 1. Re: Multiple-Join Clauses
          Manideep Bhattacharyya

          Is it possible to take out a couple of data from the excel and put that into here for our analysis ?

          • 2. Re: Multiple-Join Clauses
            Santiago Perez

            Hi Manideep, thanks for the quick reply.  I should have clarified that the joins are between postgresql tables, not excel spreadsheets. I can however run a report using all these objects to help you see the relationships, here it is attached. Columns  A-E represent an indirect investment, columns A,B,F represent a direct one. The end objective would be to have The two Account ID columns (E-F) as a single column in tableau

             

            I'm basically looking to create the following relationship

            So that users get the full list of the accounts they are invested in, whether directly or indirectly (by investing in a fund that then invests in accounts). Here's how I've tried to model it in tableau

            The key relationship, with two join clauses is displayed. I used full outer joins to begin with, I'll probably want to switch to left or right joins once I understand the functionality a bit more. That results in all the direct investments showing, but the investments__c1 table is null:

             

             

            Here's the custom sql, you can skip the long field list and just go to the FROM Statement at the bottom. If there is a fix that does not involve custom sql that would be highly desirable as I need to replicate this basic relationship in a variety of existing data connections and I would much rather stick to the Tableau visual interface rather than switch them al to custom sql. Appreciate the help!

            • 3. Re: Multiple-Join Clauses
              Dmitry Chirkov

              You should have no problems constructing a join like this in Tableau.

               

              What's the error behind that "Show Details" button?

              • 4. Re: Multiple-Join Clauses
                Santiago Perez

                This setup yields no error, but leads to the investments__c1 table being null

                And this one, which I think is equivalent, leads to null accounts__c table

                 

                So whatever gets the second join clause ends up getting all data filtered out it seems

                • 5. Re: Multiple-Join Clauses
                  Dmitry Chirkov

                  Pull query from the log file and compare to one you were writing manually.

                  • 6. Re: Multiple-Join Clauses
                    Santiago Perez

                    Here are the queries from the log file. I described above the two ways I've tried to write the query using the tableau data source builder. I'd be satisfied with either of them working if you can spot the error. The first results in an empty investments__c1 table, the second in an empty account table. Key line in both highlighted in bold

                    SELECT \"account\".\"name\" AS \"name (account)\",\n  \"investments__c\".\"name\" AS \"name (investments__c)\",\n  \"investments__c1\".\"name\" AS \"name (investments__c1)\",\n  \"investments__c\".\"sfid\" AS \"sfid (investments__c)\"\nFROM \"public\".\"contact\" \"contact\"\n 

                    INNER JOIN \"public\".\"funds__c\" \"funds__c\" ON (\"contact\".\"accountid\" = \"funds__c\".\"account__c\")\n 

                    INNER JOIN \"public\".\"investments__c\" \"investments__c\" ON (\"funds__c\".\"sfid\" = \"investments__c\".\"fund_investor__c\")\n 

                    FULL JOIN \"public\".\"funds__c\" \"funds__c1\" ON (\"investments__c\".\"portfolio_fund__c\" = \"funds__c1\".\"sfid\")\n 

                    FULL JOIN \"public\".\"investments__c\" \"investments__c1\" ON (\"funds__c1\".\"sfid\" = \"investments__c1\".\"fund_investor__c\")\n 

                    FULL JOIN \"public\".\"account\" \"account\" ON ((\"investments__c\".\"portfolio_company__c\" = \"account\".\"sfid\") AND (\"investments__c1\".\"portfolio_company__c\" = \"account\".\"sfid\"))\nWHERE

                    (CAST(\"contact\".\"portal_username__c\" AS TEXT) = 'sperez@bcorporation.net')\nGROUP BY 1,\n  2,\n  3,\n  4","query-hash":3611086898,"rows":0}}

                     

                     

                    "SELECT \"account\".\"name\" AS \"name (account)\",\n  \"investments__c\".\"name\" AS \"name (investments__c)\",\n  \"investments__c1\".\"name\" AS \"name (investments__c1)\",\n  \"investments__c\".\"sfid\" AS \"sfid (investments__c)\"\nFROM \"public\".\"contact\" \"contact\"\n 

                    INNER JOIN \"public\".\"funds__c\" \"funds__c\" ON (\"contact\".\"accountid\" = \"funds__c\".\"account__c\")\n 

                    INNER JOIN \"public\".\"investments__c\" \"investments__c\" ON (\"funds__c\".\"sfid\" = \"investments__c\".\"fund_investor__c\")\n 

                    FULL JOIN \"public\".\"funds__c\" \"funds__c1\" ON (\"investments__c\".\"portfolio_fund__c\" = \"funds__c1\".\"sfid\")\n 

                    FULL JOIN \"public\".\"investments__c\" \"investments__c1\" ON (\"funds__c1\".\"sfid\" = \"investments__c1\".\"fund_investor__c\")\n 

                    "SELECT \"account\".\"name\" AS \"name (account)\",\n  \"investments__c\".\"name\" AS \"name (investments__c)\",\n  \"investments__c1\".\"name\" AS \"name (investments__c1)\",\n  \"investments__c\".\"sfid\" AS \"sfid (investments__c)\"\nFROM \"public\".\"contact\" \"contact\"\n 

                    INNER JOIN \"public\".\"funds__c\" \"funds__c\" ON (\"contact\".\"accountid\" = \"funds__c\".\"account__c\")\n 

                    INNER JOIN \"public\".\"investments__c\" \"investments__c\" ON (\"funds__c\".\"sfid\" = \"investments__c\".\"fund_investor__c\")\n 

                    FULL JOIN \"public\".\"funds__c\" \"funds__c1\" ON (\"investments__c\".\"portfolio_fund__c\" = \"funds__c1\".\"sfid\")\n 

                    FULL JOIN \"public\".\"investments__c\" \"investments__c1\" ON (\"funds__c1\".\"sfid\" = \"investments__c1\".\"fund_investor__c\")\n 

                    FULL JOIN \"public\".\"account\" \"account\" ON ((\"investments__c\".\"portfolio_company__c\" = \"account\".\"sfid\") AND (\"investments__c1\".\"portfolio_company__c\" = \"account\".\"sfid\"))

                    \nWHERE (CAST(\"contact\".\"portal_username__c\" AS TEXT) = 'sperez@bcorporation.net')\nGROUP BY 1,\n  2,\n  3,\n  4","query-hash":3611086898}}

                    • 7. Re: Multiple-Join Clauses
                      Dmitry Chirkov

                      Let's get one thing straight - does the Custom SQL you attached earlier works for you, correct?

                       

                      In queries you showed I can't spot anything bad - the only thing that's different is ("investments__c1"."portfolio_company__c" = "account"."sfid") join condition moves from one FULL join to another. I always assumed that this is allowed so I'm not quite sure what's going on here.

                       

                      Queries you provided last seems to be identical... Can you give me a query for when

                      • investments_c1 is joined with funds_c1
                      • then, in same condition, investments_c1 is joined with account

                       

                      p.s. just press Update Now - this runs non-grouped query. Just search for "LIMIT 1000" in the log.

                      • 8. Re: Multiple-Join Clauses
                        khalid norat

                        Hi Santiago,

                         

                        I'm assuming from your screen shots you are pulling salesforce data into a Datawarehouse with a tool like DBAmp or similar and trying to visualise this within tableau.

                         

                        Having done a similar project for an investment bank earlier this year I understand the difficulties faced.

                         

                        It is definitely possible to join the required tables just using the joins

                         

                        We had data sources with around 20 tables joined.

                         

                        For best processing of reports it is ideal to perform and analysis or simplification of the data within the datawarehouse as this will ensure your reports run faster and are more responsive as this will play a big part in adoption rates of the reports if you are planning to render these tableau reports within the Salesforce environment.

                         

                        Hope some of this information is helpful.

                        • 9. Re: Multiple-Join Clauses
                          Santiago Perez

                          Quite right Khalid. We are using Heroku Connect to move our salesforce data to a PostgreSQL database, however tableau is only interacting with PostgreSQL so any Salesforce related complications should be out of the equation.

                           

                          I figured it out, seems obvious in hindsight. The issue is that both conditions are never true simultaneously, an investee is either direct or indirect. All I had to do in the CUstom SQL dialogue box was replace the AND in the following query to an OR:

                          FULL JOIN \"public\".\"account\" \"account\" ON

                          ((\"investments__c\".\"portfolio_company__c\" = \"account\".\"sfid\") AND (\"investments__c1\".\"portfolio_company__c\" = \"account\".\"sfid\"))

                           

                          So the million dollar question: Is it possible to generate an "OR" condition without relying on Custom SQL? As I mentioned before, this would be highly undesirable as I need to propagate this change to multiple connections, Tableau recommends avoiding custom SQL when possible due to performance impact, and we quite like using the visual interface whenever we need to make a change.

                          • 10. Re: Multiple-Join Clauses
                            Dmitry Chirkov

                            Not directly.

                            Can try replicating same condition via filter? (Boolean calculation filtered on TRUE)

                            • 11. Re: Multiple-Join Clauses
                              Santiago Perez

                              Hi Dmitry, could you elaborate? I thought Tableau did not support using calculated fields in a join clause? If it did, I think I would want the following:

                              ifnull( [Portfolio_Company__C](Investments__C), [Portfolio_Company__C (Investments__C1)] )

                              But I'm pretty sure that won't work. Let me know what you were thinking with the Boolean. Another avenue might be trying using a union of two queries, I think Tableau 10 supports that now

                              • 12. Re: Multiple-Join Clauses
                                Dmitry Chirkov

                                Sorry it took me a while to get back toy you.

                                 

                                Join clauses can be viewed as just filters on top of cross join but you are right - I gave it more thought and it won't work in you case. Sorry :/

                                 

                                p.s. Are you aware that you can replace part join of relation with Custom SQL, not necessarily the whole thing?

                                • 13. Re: Multiple-Join Clauses
                                  Santiago Perez

                                  Thanks Dimitry. I did indeed experiment with just using custom sql for the troublesome join, which limited the damage. However in the end I took Khalid's advice and fixed it in the database by creating a view that included both fund tables.

                                   

                                  Thanks everyone for your help, too bad Tableau didn't have a native solution!