3 Replies Latest reply on Jul 25, 2016 12:30 AM by Tableau kumar

    Record data duplicate when join 2 sheet

    Duong Vu

      dear everybody.

      I have 2 sheet, 1 sheet have quantity product of daily. 1 sheet have detail defect product of daily by section & type of defect.

      when I join 2 sheet with key : date.

      quantity product duplicate many with quantity defect by daily

      => when I sum(quantity product) _daily = (quantity product )x (quantity defect)  => wrong result

      I also make 1 calculate field = sum(quantity product)/count(date)

      result of daily is correct, but result of monthly is wrong.

       

      who can help me ?

      thank so much.

      sorry, I can't upload any field.

       

       

      DateQ'ty product
      1-1-20160
      1-2-20163699
      1-3-20160
      1-4-20164204
      1-5-20163568
      1-6-20163606

       

      dateSectionCause DepartmentDefectQuantity defect
      1-2-2016APart Damageasdg1
      1-2-2016APart Damageasg1
      1-2-2016APart Damagew1
      1-2-2016APart Damagee1
      1-2-2016BWrong Assemblyasg1
      1-2-2016BDefect Part Deliverye1
      1-2-2016APart Damageawt1
      1-2-2016BPart Damagesad1
      1-2-2016APart Damageer1
      1-4-2016APart Damageweg1
      1-4-2016BPart Damagead1
        • 1. Re: Record data duplicate when join 2 sheet
          Simon Runc

          hi  Duong,

           

          So I assume you know why it is creating duplicate records? if not post back here and I can explain.

           

          Now onto the solution...so I assume you need both Item and Defect type for your analysis. So this means you have date a different levels of grain (or granularity), and this is where data blending is ideal for this task. Below is a link to a blog on exactly this problem

           

          The Importance of Granularity (…to Blend or not to Blend?) | The Data Animators

           

          Hope it helps, and makes sense, but let me know if you have any other queries.

          1 of 1 people found this helpful
          • 2. Re: Record data duplicate when join 2 sheet
            Duong Vu

            Dear Mr. Simon

            thank so much , this is thing what I need now.

             

            my company has just buy tableau, I'm begin of tableau

            thank so much again.

            • 3. Re: Record data duplicate when join 2 sheet
              Tableau kumar

              As Simon Runc  suggested, You have to add multiple joins between (Among) tables.

               

              For an instance

              Table 1 having Country, Sales fields (Different countries have different currencies)

              Date                    Country     Currency     Sales

              01-Jan-2016          India          INR               200

              01-Jan-2016          USA          $                    500

              01-Jan-2016          UK            #                    1000

              02-Jan-2016          India          INR               200

              02-Jan-2016          USA          $                    500

              02-Jan-2016          UK            #                    1000

              03-Jan-2016          India          INR               200

              03-Jan-2016          USA          $                    500

              03-Jan-2016          UK            #                    1000

               

               

              Assume, we have another table that is having Currency conversation rate. (Converting into USD)

              Date                    From_Country     To_Country     Conversation Ratio

              01-Jan-2016          USA                    IND               1/60

              01-Jan-2016          USA                    UK                1.5

              01-Jan-2016          USA                    USA               1

               

              02-Jan-2016          USA                    IND               1/65

              02-Jan-2016          USA                    UK                1.55

              02-Jan-2016          USA                    USA               1

               

              If we want to show the data in USD, We have to define the joins at Country level and Date level since Conversation rate is vary from date to date as well as country to country.

               

              [Table1].[Country] = [Table2].[To_Country]

              and

               

              [Table1].[Date] = [Table2].[Date]

               

              If we map Country' only, we dont get right figures.

               

              I hope this is crystallized you.

               

              Best Regards

              Kumar