Overlapping of Dates

2 posts / 0 new
Log in or register to post comments
Last post
Thu, 03/08/2018 - 07:34
#1
iceshock
Offline
Joined: 03/08/2018 - 07:30
Overlapping of Dates

Hello, my current data include the travel's start date and end date. 
 
I hope to identify duplicate entries(employee ID) with all overlapping travel date. May i know how can i go about doing it?
 
 
regards,iceshock

Top
Thu, 03/08/2018 - 15:44
Steven Luciani
Offline
Joined: 07/31/2012 - 13:20

Hi Iceshock,

You can do this with the equation below. Prior to applying the equation you must index your file by employee ID and start date. Once this is done, create a virtual numberic field using this equation:

@if(EMPNO=@GetNextValue("EMPNO"),@age(@GetNextValue("start_date"),END_DATE),0)

Then you can isolate on any negative numbers. Any employee with negative numbers have overlapping travel dates.

See quick sample below.

Cheers,

Steve

Top