10 Replies Latest reply on Dec 3, 2013 5:49 PM by Julius

    Data blending and full outer join

    Julius

      Has anyone been able to trick the data blend to look like a full outer join?

       

      Yes, I do realize that data blending is not really a true SQL join and that I could create a data connection with multiple tables full outer joined. In my requirement I want to be able to do a data blend and show results similar to what I would get with a full outer join. As it is, data blend is an implicit left join of the primary with the non-primary.

       

      Please also let me know if this is an impractical request if that is the case.

       

      Thank you.

        • 1. Re: Data blending and full outer join
          Matt Lutton

          You said it yourself--you can do this using Custom SQL or the multiple tables option; so I don't understand the need? What is keeping you from utilizing these, or a View in a DB?

          • 2. Re: Data blending and full outer join
            Julius

            There are plenty of scenarios where you can't SQL-join different data sources.

            • 3. Re: Data blending and full outer join
              Matt Lutton

              Yes, there are. But in your thread you said "...and that I could create a data connection with multiple tables...", so what is it about your scenario?  Is it because you have data from two different types of data sources?

               

              You haven't described what the problem is, or why you need this.  More detail is needed to address whatever the problem is.  As always, sample data or a packaged workbook will help you get the best help possible on this forum.

               

              Best of luck.

              • 4. Re: Data blending and full outer join
                Julius

                I understand what you mean, but think of a use case where you have data in an RDBMS and related data in say, Hadoop Hive. If I have to ETL/Talend/Alteryx them into another combined store I lose the agility that Tableau gives me with data blending.

                 

                It raises the question why Tableau designed just for implicit left-joins in a data blend. Is it a practical reason or a design reason?

                • 5. Re: Data blending and full outer join
                  Nay Lin Soe

                  Hi Julius,

                   

                  I was in a similar situation and also wondering about what you have asked. In my case, it is a right-join. I have to work with data extracts. Since the data set is large, I thought blending 2 data sources would give a good balance - reducing the size of the extract without substantially losing the performance.

                   

                  Crucially though, I couldn't do a right-join. So I have to go the way of custom SQL (which results in an enormous data extract). It would be great if Tableau supports all types of SQL joins in data blending.

                   

                  Nay

                  • 6. Re: Data blending and full outer join
                    Julius

                    Hi Nay,

                     

                    I think if you swap your secondary data connection as the primary, you may be able to do it (just flipping the right-join to left-join). One-sided outer joins are determined by which is primary. The first field you put on the canvas defaults the data source as primary. You may want to google for Joe Mako's data scaffolding tricks.

                     

                    Yes, I'm still wondering why data blend evolved to be a one-sided outer join.

                     

                    Julius

                    • 7. Re: Data blending and full outer join
                      Daniel Seisun

                      Hey Julius,

                       

                      Unfortunately no, you're pretty much hosed when it comes to wanting that sort of join although I agree it'd be super useful. The trick I've managed in certain cases is to blend the two datasets into a super set of the data. As an example if you were going join two tables on a date column, I'll create a connection to a date dimension and then blend the two sources into it. It's clunky but it works in certain instances.

                       

                      Daniel

                      1 of 1 people found this helpful
                      • 8. Re: Data blending and full outer join
                        Matt Lutton

                        I would imagine they used this type of blending because it worked well for the majority of their customers' scenarios at the time.  I also agree that more flexibility is always nice and we want as many types of data sources, joins, blends, etc. as possible--but I am also sure that getting there is not a simple problem to solve.

                         

                        I also agree that switching the secondary to the primary makes sense in the scenario posted by Nay.

                        • 9. Re: Data blending and full outer join
                          Nay Lin Soe

                          Julius, I thought that much, however there is an action filter in place whose target is this blended sheet. The filtering (targeted) field resides in the 'left' data source. From what I have tried, the filter actions only work on the primary data source. So I'm required to make the 'left' one primary, and hence my problem.

                           

                          Nay

                          • 10. Re: Data blending and full outer join
                            Julius

                            Version 8.x now allows filtering on the secondary. Anthony Krinsky had this blending workshop with v8's new features: Tableau blending workshop