8 Replies Latest reply: Sep 16, 2013 3:31 AM by Pradeep Sadhasivam RSS

    Check Duplicate Records

      Hi all,

      I am working on a database to design a report consisting duplicate records:



      Suppose below is the database table:


      EmpID     ACDID    ShiftStartDate       ShiftEndDate

      1              101         02/03/13                12/03/15

      1              102         02/03/13                12/03/15

      2              103         01/01/13                02/02/13

      2              104         02/03/13                02/03/15



      So in the report I want :


      EmpID      ACDID     ShiftStartDate       ShiftEndDate

      1              101            02/03/13              12/03/15

      1              102            02/03/13              12/03/15


      Note: I don't want 3rd and 4th record as it seems to be duplicate but it is not as one of its Shift has been ended earlier than today.


      For this I wrote a script:




          REF_PayrollID as EmpID,



          if(ShiftEndDate=max(ShiftEndDate),1,0) as ShiftFlag



      Resident TeleoptiEmployee  

      group by REF_PayrollID,ShiftEndDate;





      if(count(EmpID)>1,EmpID AS DuplicateCountTeleoptiID

      Resident DuplicateTeleoptiTemp

      where ShiftFlag=1

      Group by EmpID; 

      DROP Table DuplicateTeleoptiTemp;




      But it is not working , it is showing 3rd and 4th record also.

      How to solve it ?




        • Re: Check Duplicate Records


             In  the Example shown above it looks the EMP ID  1 and 2 are Duplicated.


          Any how if you wanted to show only Unique EMP ID then you can use the below query


          SELECT *  FROM TableName

          group By EMPID having count(EMPID)=1 ;






            • Re: Check Duplicate Records

              Hi Yusuf,

              I have mentioned above what I want in my report.





                • Re: Check Duplicate Records
                  ajeet tomer

                  Hi Vijit,


                  u please fetch record from ur database first and if it work fine put it into qv datamodel. as with below sql script if u r want duplicate records from one table (employee).


                  Select e.empid , e.acdid, e.shiftstartdate, e.shiftenddate

                  From emp e

                  Where e.rowid>(Select Min(rowid) from emp f

                  //where not e.rowid=(Select Min(rowid) from emp f

                  Where f.empid=e.empid);


                  if u want to join two tables emp and dept, then make changes in subquery as


                  select e.empid, e.accdid, e.shiftsratdate, d.empid

                  from employee e, department d

                  inner join (select e.empid, d.empid, count(*) as empcount

                  from employee e, department d

                  group by empid

                  having count(*) >1 )

                  d on e.empid = d.empid

              • Re: Check Duplicate Records
                Vishwaranjan Kumar

                hi see attachement.


                firstly in your script write this syntax



                load *,if(Previous(ShiftStartDate)=ShiftStartDate,1,RangeSum(1,peek(flag))) as flag  ;


                LOAD * INLINE [

                    EmpID,     ACDID,    ShiftStartDate,       ShiftEndDate

                    1,              101,         02/03/13,                12/03/15

                    1,              102,         02/03/13,                12/03/15

                    2,              103,         01/01/13,                02/02/13

                    2,              104,         02/03/13,                02/03/15





                now take a pivot chart


                CalculatedDimension1 --   if(flag=1,flag)    // and check on supress null value

                Dimension2 -- ACDID


                Expression1---   EmpID,

                Exression2----   ShiftStartDate

                Expression2--   ShiftEndDate


                then output like this