8 Replies Latest reply on Jan 12, 2017 1:14 PM by suhas doke

    Getting Duplicate Entries with 1 to Many Relationship

    Paul Heering

      I am new to tableau and I am not a database expert so excuse me if this is a simple question or if I am using the wrong terminology (corrections are welcome).

       

      I have a data source that is set up like the explanation below (image)

      Screen Shot 2017-01-12 at 8.04.22 AM.png

       

      I can't get the desired final result.  I can get the following things (neither works for me)...

       

      A.  10 rows of data but each row only shows one code label

      B.  30 rows of data with each billing entry repeating 3 times (because each has 3 code labels)

       

      I tried the steps outlined in the link below but it didn't work for me.  I am not sure if I didn't follow the steps correctly or it is the wrong steps.

       

      Linking Tables Using One-to-Many Relationships | Tableau Software

       

      I am using tableau 9.3.3 on a Mac and I have attached a packaged workbook.

       

      Thanks in advance for any help.

       

      Paul

        • 1. Re: Getting Duplicate Entries with 1 to Many Relationship
          Wim Kegels

          Hi Paul,

           

          Getting 30 rows seems to offer the best analytic experience in this case.  By joining on code ID I managed to create following view:

          Are there specific reasons why you'd like to only have 10 rows?

           

          Kind regards,

          Wim

          • 2. Re: Getting Duplicate Entries with 1 to Many Relationship
            Paul Heering

            Wim,

             

            Thanks for responding.

             

            It might be the wrong approach but I was trying to get 10 rows was because the main metric here is hours.  If have 3 rows per billing entry the aggregate numbers for hours are off (tripled).

             

            So if I try to look at how much Paul billed on January 1st it will tell me 9 but it was only 3.

             

            Paul

            • 3. Re: Getting Duplicate Entries with 1 to Many Relationship
              Wim Kegels

              Hi Paul,

               

              I see.  there are several ways to deal with this:

               

              1. Group your data in your source: concatenate all the fields that multiplicate your data in one field.  Use calculated fields in Tableau ( CONTAINS() for example, to search for specific cases).

              2. Blend your data: link the sheets Billing table and Coding Table, and add the same datasource again (data - new data source) to add the sheet Code Label Total.

              For more information on blending: http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#multiple_connections.html 

              Blending your data will result in multiple values for the same row, and show asterisks.

              3. If you know you'll always have 3 rows for the same date entry, simply divide the hours by 3.  you can do this by double clicking a pill in the columns/rows shelves and adding /3. 

              4. use Level of Detail calculations.  In a fully joined data source (30 records), the following resulted in the correct number of hours / employee:

              sum({ FIXED [Date], [Client]: MIN([Hours])}).  This calculation looks per date and per client, then selects the minimum amount of hours (which is the same for all three entries for that client on that date) and takes the sum over the whole dataset.

               

              Hope this helps!

               

              Kind regards,

              Wim

              2 of 2 people found this helpful
              • 4. Re: Getting Duplicate Entries with 1 to Many Relationship
                Paul Heering

                Wim,

                 

                Thanks for the detailed and speedy response.

                 

                Based on your ideas.

                 

                Option #1--I have no control of the source data.  In the real life example, I am connecting to redshift data. I just made up these excel sheets to simplify the data and take out private information.

                 

                Option #2--I think this might be the way to go, I will try it out.

                 

                Option #3--I almost typed it in my original post but the number of labels varies, it can be anywhere between 1 and 9.  So I can't just divide by 3.  Although, I wish I could, that seems like the simplest solution.

                 

                Option #4--This might work too.  Just wondering what would happen if you met with the same client twice in one day?  Would that calculation still work?  So for example if Paul (staff) met with John (client) twice on Monday so there are two billing entries in the original source.  Both appointments were for 1 hour.  Now we would end up with 6 rows (3 rows each for each original row).  Would the aggregate number be 1 (pulling in minimum) or would it somehow know that it should be 2?

                 

                Thanks again for all your help.  I'm new to the forums so I am not sure the best way to give "points" or credit for helping but I will do my best.

                 

                Paul

                • 5. Re: Getting Duplicate Entries with 1 to Many Relationship
                  Wim Kegels

                  Hi Paul,

                   

                  I just noticed option 4 can be simplified even further:

                   

                  sum({ FIXED [Biling ID #]: MIN([Hours])})

                  This will check the biling ID (which should be unique) and return the sum of all the minimum values per billing ID. 

                   

                  Using this calculation will allow you to create stuff like this:

                  If you're new to Tableau, Level of Detail calculations might seem complex (they kinda are), but they are really powerful tools.  I sugest looking into them if you're serious about using Tableau in the future.

                   

                  Kind regards,

                  Wim

                  1 of 1 people found this helpful
                  • 6. Re: Getting Duplicate Entries with 1 to Many Relationship
                    Veronica Simoes

                    Hi Paul,

                     

                    Follow the results that you need :

                     

                    Sheet 1.png

                    4 of 4 people found this helpful
                    • 7. Re: Getting Duplicate Entries with 1 to Many Relationship
                      Paul Heering

                      Thanks Veronica, sorry I didn't give more details, but I don't think that solution works because when I try to make a chart of these data I am still having the issue where it adds them all up, for 1/1/17 it says Paul did 9 hours if I put sum of hours on rows and it says Paul did 1.5 hours if I put average of hours on rows.  Neither is correct.  He provided 3 hours of services on 1/1/17 (one appointment for 1 hour and another for 2 hours).

                       

                      I'm betting the issue is that I am just implementing your solution incorrectly but I can't get it to solve the problem I'm having.

                       

                      Paul

                      • 8. Re: Getting Duplicate Entries with 1 to Many Relationship
                        suhas doke

                        Hi Paul,

                           I think it's better to do it in SQL rather than Tableau

                        You can use following query to get your data as you desire then you don't have to worry about LODs and Aggregations

                        Here is the Redshift code to get the way you like it

                        SELECT bt.*,ct.CODE_NAME, lt.LABEL FROM BILLING_TABLE bt

                        LEFT JOIN CODE_TABLE ct ON ct.CODE_ID = bt.CODE_ID

                        LEFT JOIN

                        (

                        SELECT CODE_ID,
                          LISTAGG
                        (CODE_LABLE,', ')
                        WITHIN GROUP (ORDER BY CODE_LABEL)
                        OVER (PARTITION BY CODE_ID) AS LABEL
                        FROM CODE_LABEL_TABLE
                        ORDER BY CODE_ID

                        ) lt     ON lt,CODE_ID = bt.CODE

                         

                         

                        I know this is not Tableau solution but sometimes it helps to look out of Tableau!!

                        Good Luck!!

                        Suhas