3 Replies Latest reply on May 9, 2016 7:56 AM by Andrew Watson

    Eliminate duplicate row and correcting calculation within tableau

    Sanjay Nimbark

      Hi Community,

       

      we got SQL data source and joined couple of view due to that we have duplicate rows and calculation goes wrong, I can not change the data layout so looking for some calculation to correct it.

       

      Requirement : Each PROFILEID , TAKE MAXIMUM ASSESSMENTINSTANCE ID , AND found how many have back problem , in data we store 0 and 1 ,0 means no and 1 means yes, so by simiply sum it should have 171  but because of duplicate value i am getting different value 180.

       

      I have tried couple of calculation to achieve this

       

      { FIXED [Profileid]:Max([Assessmentinstanceid])} to find max assessment and applied filter so it filter out rest, but no luck then i applied create

      calculation2 as { FIXED [Profileid],[Max_Assessmentinstanceid]:max([Back Problem])}  As Back problem got only 1 and 0 so should get one value and then top sum but it give me 176 i have attached screen shot which show strange result even value got 0 it take as 1.

       

      is there any way to achieve and eliminate duplicate and count as per or any help to understand behaviour would much appreciate.

       

      I have attached data sheet , screenshot and workbook

        • 1. Re: Eliminate duplicate row and correcting calculation within tableau
          Andrew Watson

          I can't open your workbook (version issues), however think this can be solved without opening...looks like you are on the right track with your first LOD calc. Expand it slightly to be:

           

          IF [Assessmentinstanceid] = { FIXED [Profileid]:Max([Assessmentinstanceid])} THEN 'SHOW' ELSE 'HIDE' END

           

          Drag that to the filters shelf and filter for SHOW.

           

          Also filter for those who only have a back problem. If you Count Distinct Profile ID you get 171 profile Ids with a back problem.

           

          Note SurveyGroup and ActionReferenceId are actually causing a big chunk of your duplication hence why I had to COUNTD rather than just sum the column.

           

          You could alter your definition of uniqueness by combining the Assessment ID and Action Ref ID to produce a new key field and use that:

           

          IF [Assessmentinstanceid]+INT(IFNULL([Action Reference ID],'0')) = { FIXED [Profileid]:Max([Assessmentinstanceid]+INT(IFNULL([Action Reference ID],'0')))} THEN 'SHOW' ELSE 'HIDE' END

           

          Putting that on the filter shelf means you can sum the back problem column and come out with 171.

           

          9.2 workbook is attached containing both solutions - hope this solves your problem.

          1 of 1 people found this helpful
          • 2. Re: Eliminate duplicate row and correcting calculation within tableau
            Sanjay Nimbark

            Hi Andrew ,

             

            Thanks for your solution, I had done first IF [Assessmentinstanceid] = { FIXED [Profileid]:Max([Assessmentinstanceid])} THEN 'SHOW' ELSE 'HIDE' END which is already placed when I upload question and I tried the Backproblem filter but I am doing word cloud and bringing all other medical condition within same sheet will not help

             

             

            the second solution is perfect it resolve the problem

             

            IF [Assessmentinstanceid]+INT(IFNULL([Action Reference ID],'0')) = { FIXED [Profileid]:Max([Assessmentinstanceid]+INT(IFNULL([Action Reference ID],'0')))} THEN 'SHOW' ELSE 'HIDE' END

             

            Thanks very much for your help

            • 3. Re: Eliminate duplicate row and correcting calculation within tableau
              Andrew Watson

              Please mark the answer as correct to close out the thread. Thanks.