Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Yusuf,
I have mentioned above what I want in my report.
Thanks
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 |
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
here
i made all duplicate entries on the basis of flag.
when flag =1 then you get all duplicate entries
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() ;
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
Dear Vishwaranjan and all,
Unknowingly i click on report abuse link. Is there any way to cancel this.
Best Regards,
Pradeep