1 Reply Latest reply on Sep 8, 2012 12:52 PM by Wilson Po

    Remove old duplicates data or result to show 0

    Michael Sonu
      chk1.png
      Hi,
      I would like to remove the duplicates or to get the result as 0 for the duplicates based on the date.
      For the above example:
      I would like to remove either Ramesh & Michael with old date or to get the result as "0" for the names that have been entered twice with old date.
      Thanks
      Michael




























































      NameDate ReportedResult
      Ramesh1-Jan-120
      Suresh31-Dec-121
      Michael9-May-121
      John2-Jun-121
      Bhaskar3-Aug-121
      Ranjith3-May-121
      Ansh5-Jun-121
      Jackson2-Apr-121
      John6-Jan-121
      Ramesh2-Mar-121
      Michael4-Feb-120
      Name
      Date Reported
      Result


      Ramesh
      1-Jan-12
      0


      Suresh
      31-Dec-12
      1


      Michael
      9-May-12
      1


      John
      2-Jun-12
      1


      Bhaskar
      3-Aug-12
      1


      Ranjith
      3-May-12
      1


      Ansh
      5-Jun-12
      1


      Jackson
      2-Apr-12
      1


      John
      6-Jan-12
      1


      Ramesh
      2-Mar-12
      1


      Michael
      4-Feb-12
      0



























































      NameDate ReportedResult
      Ramesh1-Jan-120
      Suresh31-Dec-121
      Michael9-May-121
      John2-Jun-121
      Bhaskar3-Aug-121
      Ranjith3-May-121
      Ansh5-Jun-121
      Jackson2-Apr-121
      John6-Jan-121
      Ramesh2-Mar-121
      Michael4-Feb-120
        • 1. Re: Remove old duplicates data or result to show 0
          Wilson Po

          Hi Michael,

           

          I was wondering what you were trying to achieve with this?  The most way is to actually just use a COUNTD() aggregation on the [Name] field, which will only count each name once, if the intent is to roll it out somehow. Otherwise, if we want to display the table as you have it above, a table calculation would handle this; to do this please follow the following steps:

           

          (1) Create a calculated field with the formula: IF INDEX()=1 THEN 1 ELSE 0 END

          (2) Add the field to view, right click on it and "Edit Table Calculation"

          (3) Under the "Advanced..." option, compute using (in this order):

          Name

          Date Reported

          (4) Back at the "Edit Table Calculation" dialog, set "Restarting Every" to be Name

           

          If this is done correctly, we should have a field that should now flags the first time the name shows up with a 1 and leaves every additional time that the name shows up with a 0. Do note this method only works when both Name and Date Reported is used in the view.

           

          Hope this helps!

           

          Wilson