Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Check Duplicate Records

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

Re: Check Duplicate Records

Hi Yusuf,

I have mentioned above what I want in my report.

Thanks

Not applicable

Re: Check Duplicate Records

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

Re: Check Duplicate Records

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

Re: Check Duplicate Records

here

i made all duplicate entries on the basis of flag.

when flag =1 then you get all duplicate entries

Not applicable

Re: Check Duplicate Records

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
New Contributor III

Re: Check Duplicate Records

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

Re: Check Duplicate Records

Dear Vishwaranjan and all,

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

Best Regards,

Pradeep