topic Re: Check Duplicate Records in QlikView

Check Duplicate Records

Tue, 28 May 2013 10:18:50 GMT

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

Tue, 28 May 2013 10:40:23 GMT

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

Tue, 28 May 2013 11:30:34 GMT

Hi Yusuf,

I have mentioned above what I want in my report.

Thanks

Re: Check Duplicate Records

Tue, 28 May 2013 11:36:18 GMT

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

Flag	ACDID	EmpID	ShiftStartDate	ShiftEndDate
1	101	1	02/03/13	12/03/15
1	102	1	02/03/13	12/03/15

Re: Check Duplicate Records

Tue, 28 May 2013 12:01:02 GMT

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

Re: Check Duplicate Records

Tue, 28 May 2013 12:04:43 GMT

here

i made all duplicate entries on the basis of flag.

when flag =1 then you get all duplicate entries

Re: Check Duplicate Records

Tue, 28 May 2013 12:13:42 GMT

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() ;

Re: Check Duplicate Records

qlikviewajeet — Wed, 29 May 2013 02:02:08 GMT

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

Mon, 16 Sep 2013 07:31:06 GMT

Dear Vishwaranjan and all,

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

Best Regards,

Pradeep