Detect overlapping date range

Hi,

I’ve a table with “% of discount”

Product_______Start_________End_________%
Prod1______2020-01-01____2020-12-31_____1.5
Prod1______2021-01-01____2021-06-30_____2.0
Prod2______2021-01-01____2022-02-28_____1.8
Prod2______2022-01-01____2022-06-30_____2.0

I want to detect for the product 2 the overlap from 01/01/2022 to 28/02/2022 where the 2 discounts will cumulate if I do nothing.

I tried a lot of things but working with dates is so complicated…

Note that the date are already defined as “Date” in knime via String to Date&Time.

Do you have an idea how to proceed because I’m completely stuck.

Thank you.

Hi does this workflow help?

KNIME_overlapping_date_range.knwf (27.0 KB)

It joins the input data to itself on “product” which is kind of a cross-join but not quite a full cross join. It uses a rule to determine if the dates overlap (ignoring where a row has been joined to itself), and marks the overlap with a new column. It then joins those rows marked as overlapped back with the original data set, puts back the original rowids that somehow got mixed up (maybe some other way of doing that bit but I was in a hurry!) and returns at the end with the original data set plus a new “is overlapped product” indicator showing Y or N for each row.

There might be other ways to do this, but this was me putting it down as I thought of each step, and hopefully can give you some pointers.

5 Likes

Hi,
Wow I’m so impressed! That’s exactly what I wanted!
A big big thanks.
Now I’m trying to understand the logic to not just becoming a script kiddie :slight_smile:
Thanks a lot.

2 Likes

Feel free to ask questions about it. Glad it helped.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.