topic Re: Overlap dates in QlikView

Overlap dates

paulwalker — Sat, 16 Nov 2024 00:48:00 GMT

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!

Re: Overlap dates

rubenmarin — Wed, 22 Apr 2020 11:18:37 GMT

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;

Re: Overlap dates

paulwalker — Wed, 22 Apr 2020 17:31:57 GMT

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

Re: Overlap dates

rubenmarin — Wed, 22 Apr 2020 16:26:16 GMT

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.

Re: Overlap dates

paulwalker — Wed, 22 Apr 2020 16:43:07 GMT

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

Like, StartDate&RowNo() as %Key

now working fine all the cases!

Thanks Ruben 🙂