Find a date with in the date range

[udaya_kumar]

Hi,

I have data similar like this

Name	Start Date	End Date	Value
Uday	1-9-2018 10:30:12	1-9-2018 10:35:19	10
Uday	1-9-2018 10:32:12	1-9-2018 10:33:12	20
Uday	1-9-2018 10:37:12	1-9-2018 10:39:12	20
Kumar	1-9-2018 10:31:12	1-9-2018 10:40:45	10
Kumar	1-9-2018 10:33:12	1-9-2018 10:45:12	15
GS	1-9-2018 10:30:19	1-9-2018 10:41:12	30
GS	1-9-2018 10:34:12	1-9-2018 10:35:11	25
GS	1-9-2018 10:36:17	1-9-2018 10:37:12	10
GS	1-9-2018 10:42:12	1-9-2018 10:43:15	34
UKGS	1-9-2018 10:30:12	1-9-2018 10:31:12	45

I want to group the data by Name and find out the overlapping dates in the data.

For example.

The Name Uday has 3 rows, where the second row's start and end date are in the first row's date range.

10:32:12 and 10:33:12 which falls under 10:30:12 and 10:35:19 (Time)

So i should mark the second row as 1 in the new field to indicate it as a overlapping time.

For example, the name Kumar has 2 rows, the second row of Kumar has the start date as 10:33:12 which falls under the first row's start and end date. So i should mark that row as 1 in the new field.

This should be done for all the rows group by the Name.

How do i achieve this in the script?

[rubenmarin]

Hi Uday, you can do a reload with order by Name, and Start Date, and use Peek() to check with previou values.

To help checking some rows above, like in the 3rd case of GS, overlapping with the first, but not with the second, you can create an auxilairy field that stores the max End Date, like:

LOAD Name,

     [Start Date],    

     [End Date],

     [Value],

     If(Peek([End Date])>=[End Date], Peek([End Date]), [End Date]) as maxEndDate,

     If(Peek(Name)=Name and [Start Date]<=Peek(maxEndDate), 1, 0) as isOverlapped

Resident tableName Order By Name, [Start Date];

[udaya_kumar]

Hi Ruben,

Thank you for your reply.

I am trying your code, but I am not getting any data for Peek([End date]) so the maxEndDate is giving wrong value and Overlapped is showing all rows as 0.

I just tried Peek([End Date]) as New_EndDate in the script, but it is not giving any value, it gives null.

If i use Previous([End Date]), then it is giving me the value.

[rubenmarin]

Hi Uday, I tested it and seems working. Check that your timestamps are real timestamps and not just strings.