1 Reply Latest reply on Feb 21, 2013 8:55 PM by Joshua Milligan

    Is there a way to successfully join tables with a 1 to many relationship without using custom SQL?

    Matt Pupa

      Is there a way to successfully join two tables with a 1 to many relationship without using custom SQL?

       

      I have a table with a customer ID and another table with order date that joins on customer ID. I'm trying to figure out whether or not the customer had an order date. Normally in SQL I'd write a case statement like the one below....

       

      SELECT

           DISTINCT CUSTOMER_ID

           ,SUM(CASE WHEN ORDER_DATE IS NOT NULL THEN 1 ELSE NULL END) ORDERS

      FROM TABLE1 T1

      LEFT JOIN TABLE2 T2

           ON T1.CUSTOMER_ID = T2.CUSTOMER_ID

       

      From there, I'd take the count of customers with atleast 1 order date.

       

      When I join my customer table to the order table in tableau, I get all of the records from both tables, which is not what I want.

        • 1. Re: Is there a way to successfully join tables with a 1 to many relationship without using custom SQL?
          Joshua Milligan

          Matt,

           

          I assume that in Tableau you connected to the database, selected the multiple tables option and setup your join.  At that point, what you've done is given Tableau all the metadata it needs to build a visualization based on what fields you include on different shelves (Rows, Columns, Marks, Filters).

           

          It will build a query to send to the underlying database, which may very well look like the custom SQL you wrote, depending on where you drag and drop the various fields as you build your viz.  The beauty of it (once you adjust to the paradigm) is that you don't have to think in terms of SQL -- you start thinking naturally about how you want to shape your data into a visualization.  (And since you understand SQL it will get really fun once you adjust to the Tableau way of thinking because you'll have a deeper understanding of what's going on and how to use it in a powerful way).

           

           

          So, to get what you are looking for above, do something like this:

          1. connect to the data, select multiple tables, and specify the left join

          Then, In a new view:

          2. Put Customer_ID on the Rows Shelf

           

          At this point, you will see a distinct list of Customer_ID, because Tableau is ready to aggregate to the customer level.  The underlying query likely contains a GROUP BY Customer_ID

           

          3. Right click Order_Date in the dimensions list and select "Create Calculated Field" and use code like this:

          SUM(IF ISNULL(Order_Date) THEN NULL ELSE 1 END)

           

          4. Put that calculated field on the Columns shelf.

           

          At that point, the query that Tableau is sending to the database is very similar to your custom SQL.  It won't be pulling every row and then doing calculations and computations -- it will be letting the underlying database do the heavy lifting and then building a nice chart for you with aggregated data.  And as you add and adjust your visualization, Tableau will be re-querying the database as necessary (or using a cache if it can), with slightly different queries -- but always at the highest level of aggregation possible given what you are telling it you want to see.

           

          5. Put the same calculated field on the filter and tell it "At Least" 1.  That will filter out any customer who does not have at least one order date.

           

          Hope that helps!  I'd love to answer any other questions you might have!

           

          Regards,

          Joshua