Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

8 Replies
Not applicable
Author

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

Not applicable
Author

Hi Yusuf,

I have mentioned above what I want in my report.

Thanks

Not applicable
Author

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
Not applicable
Author

Hi

What you have written is  when ShiftStartDate is equal the previous one , but I don't want this ,

I want the duplicate EmpID who has multiple entries and whose ShiftEndDate in all the multiple entries greater than today.

Hope you understand the problem.

thanks

Not applicable
Author

here

i made all duplicate entries on the basis of flag.

when flag =1 then you get all duplicate entries

Not applicable
Author

then simply gives where condition.

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

] Where ShiftEndDate>Today() ;

qlikviewajeet
Contributor III
Contributor III

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

Not applicable
Author

Dear Vishwaranjan and all,

Unknowingly i click on report abuse link. Is there any way to cancel this.

Best Regards,

Pradeep