10 Replies Latest reply on Mar 24, 2015 10:11 PM by Tableau kumar

    Data Blending Or Joining?

    derek.wong.1

      I am trying to display a chart that would include all unique department names from two different tables.  There are department names that are common on both tables, unique on Table1 (Worksheet 1 in my attached workbook) and Table 2 (Worksheet 2). Worksheet1 one has Paid Amount and Worksheet2 has Invoice Amount.

       

      My goal is to display all unique department name and its paid and invoice amount and if the amount is null on the other table, display zero.  Right now I could now display department from one table but not both using data blending.  I have tried joining with no luck.

       

      Any help would be greatly appreciated!

       

      Derek

        • 1. Re: Data Blending Or Joining?
          Alexander Mou

          Use left join or right join.

          Not inner join.

           

          On Tuesday, March 24, 2015, Derek Wong <tableaucommunity@tableausoftware.com>

          • 2. Re: Data Blending Or Joining?
            Alexander Mou

            Make a third sheet with all the depts. use it as the primary data source.

            Blend the other two with it.

            • 3. Re: Data Blending Or Joining?
              derek.wong.1

              Thanks for your suggestion Alexander.  Sorry I am still new to Tableau, how do you "make a third sheet"?  I have left joined Worksheet 1 and Worksheet 2.  But not sure how to proceed from there.

              • 4. Re: Data Blending Or Joining?
                Bora Beran

                You mean something like this? What makes this complicated is the fact that you have records on both tables you want to keep which requires a full outer join, which is not supported either by JET or the new Excel connectors. Getting the full domain via union in SQL is a possible workaround.

                • 5. Re: Data Blending Or Joining?
                  Alexander Mou

                  Here is my solution that you can download here: | Tableau Public

                   

                  1.Create an excel sheet with all the Dept names

                  2.Load it to Tableau as the third data source

                  3.Use it as the primary data source and blend with 2 other sources

                  4.Create the chart

                  5.Add label thru adding reference lines per cell, show value, format label....

                  • 6. Re: Data Blending Or Joining?
                    derek.wong.1

                    Bora,

                     

                    I see that you use PO when you left join CustomSQL with Worksheet1 and Worksheet 2.  What if my data does not have PO number.  My real data would be Department Name and Paid Amount in one table (Worksheet 3) and Responsible Dept (Worksheet 4) and Invoice Amount in another table.  The only linkage is by the name.  However if I used that to do my left join, I ended up with duplicate entries and my numbers are off.  Do you have any idea?

                     

                    Derek

                    • 7. Re: Data Blending Or Joining?
                      Bora Beran

                      It is not a matter of join type. Even if you did INNER JOIN you'd get such replication. There is no way for the query to identify which marketing department entry matches with what marketing department entry so it will join with all occurrences. You need a reliable unique identifier for things you don't want to replicate.

                       

                      If there is no other way out, you can do some tricks in calculations to avoid skewed results. Some aggregations won't get impacted e.g. MAX, MIN, AVG. For the ones that will get skewed it is possible but a really hacky workaround.

                       

                      You can add two more Custom SQL pills into the join diagram that tell you how many occurrences of a department came from both tables.

                       

                      SELECT

                        [Worksheet2$].[Responsible Dept] AS [Responsible Dept], COUNT(*) as [cnt]

                        FROM [Worksheet2$] group by [Worksheet2$].[Responsible Dept]

                       

                      SELECT

                        [Worksheet1$].[Department Name] AS [Department Name], COUNT(*) as [cnt]

                        FROM [Worksheet1$] group by [Worksheet1$].[Department Name]

                       

                       

                      If dept A appears 2 times in one table and 2 times in the other, you know you have to divide it by 2 since it will replicate twice.

                       

                      If it happens 2 times in table source but 1 or fewer in the other, you don't need to divide it by anything to deal with the skewed results since there will be no replication.

                       

                      So you need a few IF statements before you aggregate.

                       

                      Or you can pre-aggregate in the query to get rid of replication (which gives you the same effect as blending but without need for creating multiple data sources) but with the down side of having to bake in the aggregation. So it is a trade-off based on what kind of flexibility you want.

                      1 of 1 people found this helpful
                      • 8. Re: Data Blending Or Joining?
                        Ambili Suresh

                        See attached. I created a primary datasource which has unique dept name. Then left join primary with the paid and invoice worksheets to create two sep data sources. Now blend on Dept Name.

                        1 of 1 people found this helpful
                        • 9. Re: Data Blending Or Joining?
                          Alexander Mou

                          this is the same as my solution above.

                          1 of 1 people found this helpful
                          • 10. Re: Data Blending Or Joining?
                            Tableau kumar

                            Hi,

                             

                            Joining is the Better Approach than Blending when two tables located in the same Database & Same Schema, Since

                            1) If we implement Data Blending, Can't use the "Show Relevant Value option (Cascading Quick Filter),  If we use Join then it easier.

                            2) If we implement Data Blending, We can't apply the Global Filter when different reports from Different Databases, If we use Join then it easier.

                            3) Blending woks like Left Outer Join. Joining can support all kind joins (Inner, Left, Right,----)

                             

                            Best Regards

                            Laxman Kumar