8 Replies Latest reply on Aug 2, 2012 10:15 AM by Jeff Mills

    One to many...

    . ronmatt

      I'm wrestling with a one to many problem. I have a table of customers and a table of activities. I'm trying to create a view that shows the last contact with a customer. There may or may not be an activity for that customer. I can create a view that shows the last activity for the customer but now need to link that to the customer master in a way that would show either a null entry if there has been no contact or the date of the last contact. I've tried to use the activity files as a secondary data source but I'm not getting the results that I expected. Any ideas?

        • 1. Re: One to many...
          Joe Mako

          Can you provide more details, or explain how your situation is different from this situation:

           

          Customers Table:

           

          CustID,Name
          
           1,Bob
          2,Jim
          3,Sam
          4,Ned
          


           

          Transactions Table:

           

          TransID,CustID,Date
          
           1,1,1/1/2010
          2,1,6/1/2010
          3,3,7/1/2010
          4,4,8/1/2010
          5,4,9/1/2010
          


           

          With the customer's table as the primary (with blue check mark) data source in a data blend, you should be able to get what you are asking for as in the attached workbook using the above data.

           

          In this case if you want to have the data blend relationship on "CustID", it will need to be in the dimension area for both data sources for the relationship link to be made.

          • 2. Re: One to many...
            Sean Otto

            I am looking at something very similar to do as you have in your example joemako, but when I look at your example, i'm not understanding.  Here is my issue.  I have customers, transactions, and transactionitems.  When I connect Transactions (which has sales amount) to TransactionItems (which has what was purchased), it is a one to many relationship.  I do an inner join of TransItemID = TransItemID.  When I take the total amount of the sale in transaction it is accurate.  When I add in the TransactionItems Table it then duplicates the amount of sale by the number of transaction item records.  This seems odd to me.  Any thoughts?

            • 3. Re: One to many...
              Joe Mako

              sean otto,

               

              I was not able to follow your description. Can you put together a small set of data (something like what I did above) for each table, and then what you want the final result to be?

              • 4. Re: One to many...
                Bob Laverty

                I was just mulling this over myself, which brought me to this part of the forum.  SQL will do the same thing in a database query.  Any measures in a parent table will be replicated in a one-to-many join to a detailed child table, so any direct sum of those measures will be invalid.  The only way to reverse this effect is to divide the sum by the count of detail records.  Just be careful that the formula accounts for detail records that might be filtered out. 

                 

                For example, a master order record might have a final sale amount, minus discounts, that cannot be calculated from the detailed line item records.  This amount will appear as many times as the number of detailed records.  You might have a column with a count of the line items, and you could use this to divide the sum of final sale amount and obtain the original amount.  You just have to be sure that every line item is returned in the query.  Otherwise, the sum will be too small to be divided correctly.  I think you can also count the number of times each order ID appears, but I don't have an example handy.

                • 5. Re: One to many...
                  guest contributor

                  Seems I'm facing a very similar issue myself and looking for a simple elegant solution. Here's my problem.

                   

                  I'm doing a live connect to a MySQL database (I'm lucky in that I have a database designer that can help design the database to suit my needs instead of the other way around). I have 1 master table with 2 sub tables, each one has a different one-to-many relationship. Simple example follows:

                   

                  Master table

                   

                  ID, project

                  1, project 1

                  2, project 2

                  3, project 3

                   

                  Table 2

                   

                  ID, Task, Name, months

                  1, IM, dumbo, 1

                  1, DM, dumbo, 2

                  1, IM, mickey, 1

                  2, DV, dumbo, 1

                  2, IM, dumbo, 1

                   

                  Table 3

                   

                  ID, Deliverable, name, date

                  1, doc, dumbo, 1/12/2011

                  1, image, dumbo, 20/02/2012

                  1, doc, mickey, 1/12/2011

                  1, image, mickey, 1/12/2011

                  2, doc, dumbo, 15/01/2012

                   

                  My problem is, if I use the one to many joins, between the master and table 2 using a right join, the sum of months works fine. As soon as I add the second join from master to table 3 using a right join again, the sum of months is multiplied by the number entities in table 3.

                   

                  It seems to me, that if Tableau is meant to work with relational databases, this sort of conundrum should be taken into account, so I've been searching high and low for an obvious solution that I missed somewhere, but have yet to find the answer. I even tried using single table connections in v.6 of Tableau, then using the relationship feature to replicate the relationships that occur in the database but this doesn't work either. I'm at a loss, and short of asking my DB admin to program a count field in the master table that I can then use to create a calculated field to readjust the numbers, I have yet to find an elegant solution.

                   

                  Any ideas might help so don't be shy...

                   

                  Alain

                  • 6. Re: One to many...
                    guest contributor

                    Hi Alain,

                     

                    I'm having the same problem as you and I'm not able to find a solution. Have you found one ?

                     

                    It's strange that adding Table3 into the data source affects the reports that are only based on the first two tables. (Other reporting tools, like Business Objects, handle this easily.)

                     

                    Thanks for sharing,

                    Cristian

                    • 7. Re: One to many...
                      guest contributor

                      I'm struggling with the same issue.  Using Alain's example above, what I'd like to be able to do is tell tableau how the aggregation should work in detail for the "month" column of "Table 2."    I.e. when aggregated across records in the "Master" table, the aggregation should be SUM([month]) but across records from "Table 3" it should just be MIN([month]) or anything that will just return one value instead of adding them together.

                       

                      I'm certainly no expert, but I'm not sure this exists in Tableau, but it would be really cool if it did.  As it is, it's quite easy to analyze very multidimensional data as long as it's pretty much just Tableau dimensions, but if it's measures, I feel like I have to worry a lot about this issue.

                      • 8. Re: One to many...
                        Jeff Mills

                        I have created a solution to the one to many problem.  It actually has 3 solutions within it.  Let me know if this helps.

                         

                        Hope to see everyone at the conference in San Diego