Overlap dates

[paulwalker]

Hi Community

I have to create flag between dates depends on Category, let say Category A has two records

First date and Last date here dates are over lapped - 

First Record has:  First Date = 4/22/2017

                                    End Date = 4/22/2020 -  here between data 2017,2018,2019 and 2020

Second record start and date date are in between 2019, thus it has covered already in first records (means these two records are in overlapped - so my flag should be overlap)

Category	Product	first date	End date
A	A1	4/22/2017	4/22/2020
A	A2	1/23/2019	12/31/2019
B	B1	5/20/2020	7/22/2020

Hope you understand!

Thanks in Advance!

[rubenmarin · Accepted solution]

Hi Pauls, you can do load sorted by category and start date, and  use Peek() to check if start date is higher than previous end date, something like:

LOAD
  *,
  If(Category=Peek(Category), 0, 1) as flagFirstDate,
  If(Category=Peek(Category) and [first date]<Peek([End date]), 'Overlap', 'NO') as OutColumn
Resident Data
Order by Category, [first date];

And probably you will need another load in opposite order of dates to fill the first date

LOAD
  // Fields,
  If(flagFirstDate and Category=Peek(Category) and [End date]>Peek([first date]), 'Overlap', OutColumn) as OutColumn
Resident Data
Order by Category, [first date] desc;

[paulwalker]

Thanks Ruben, It seems working fine!

but, below case is not working, how to compare with below scenario?

If we have both same dates, that should be overlap - have written firstdate=Peek(firstdate) is not working for this ??

Thanks

[rubenmarin]

Hi, I don't see why that case doesn't works, first date is before previous record end date so it should be checked as overlap as nay other record with different dates.

Maybe you need to add "product" and "product desc"  in order by clause, to ensure it has been readed as it expected (first one from older to newer, second pass from newer to older.

[paulwalker]

I think issue was with same dates, thus have added RowNo()

Like,  StartDate&RowNo() as %Key

now working fine all the cases!

Thanks Ruben 🙂