Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulwalker
Creator II
Creator II

Overlap dates

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)

CategoryProductfirst dateEnd date
AA14/22/20174/22/2020
AA21/23/201912/31/2019
BB15/20/20207/22/2020

 

Hope you understand!

Thanks in Advance!

Labels (1)
1 Solution

Accepted Solutions
rubenmarin

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;

 

View solution in original post

4 Replies
rubenmarin

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

Like,  StartDate&RowNo() as %Key

now working fine all the cases!

Thanks Ruben 🙂