How to find if a date is between two dates

Hi everyone,

I am new on Knime usage and I am trying to find a solution on a problem I am actually facing on the excel file attached.

I need to know for each closed row with a specific key if there is at least one other row with the same key (both open and closed) anda start date between the end date and the external date of the previous one, inserting on the solution column a “Y” if there aren’t other keys or a “N” if there is at least one key.
Let me know if you need other info.

Many thanks
Lorenzo

Example.xlsx (11.1 KB)

Hi,
You can use the Joiner node to join the table with itself based on Key. Then you can use the Rule-based Row Splitter or Rule-based Row Filter to find the data point where a match occurs. Then you can use a GroupBy based on the key and some First() aggregations on the other fields you need to get all the rows that have a match. Finally, you can add a Y with the Constant Value Column node, filter the matches from the original table with the Reference Row Filter, add a N to the left-overs and then concatenate the two tables.
Kind regards,
Alexander

2 Likes

I don’t know if it can be helpful but, for example, rows 1,6 and 10 have the same key but row 6 has a start date between the end date and the external date of row 1(meaning that row 1 solution is a “n” and row 6 solution is a “Y”) and row 10 has a start date outside the range of the previous ones.

I’m trying to use your solution but I’m blocked at the first point.

Hi @lorenzofoschi

Does this workflow helps you out? date_between2dates.knwf (1.9 KB)


gr. Hans

2 Likes

Hi,
Why is the solution for the last line Y? There is no other A row where 2020-05-13 is between their end and external date.
Kind regards,
Alexander

The solution usually is Y usually when there aren’t lines after the last one with the same key or, if there are, if the start date is not between the end and the external date

Hi Hans,

I’m trying to set up by myself this flow but I can’t understand the set up of the second Sorter node

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