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:

       

      Ex.

      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:

       

      DuplicateTeleoptiTemp:

      LOAD

          REF_PayrollID as EmpID,

         ,

         

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

          

        

      Resident TeleoptiEmployee  

      group by REF_PayrollID,ShiftEndDate;

       

      DuplicateTeleopti:

      Load

      EmpID,

      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 ?

       

       

      Thanks

        • Re: Check Duplicate Records
          Yusuf Ali

          HI,

             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 ;

           

           

           

          Regards

          Yusuf

            • Re: Check Duplicate Records

              Hi Yusuf,

              I have mentioned above what I want in my report.

               

               

               

              Thanks

                • 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

                 

                table1:

                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

                then

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

                Dimension2 -- ACDID

                 

                Expression1---   EmpID,

                Exression2----   ShiftStartDate

                Expression2--   ShiftEndDate

                 

                then output like this

                 

                 

                FlagACDIDEmpIDShiftStartDateShiftEndDate
                1101102/03/1312/03/15
                1102102/03/1312/03/15