Filter rows based on a non-static date range (on cell A) relative to a non-static date in same row (but on cell B)

[rdlc1]

So I am trying to filter a table based on two distinct year-month columns (column A and column B). Both dates are non-static. What I am trying to do is filter the table for rows where:

1. the year-month in column A are +/- 1 month of year-month in column B
2. year-month in column A is -1 year +/- 1 month in column B.

Any insight on how to tackle this (nodes, workflow) is appreciated.

Thanks, Richard

[ArjenEX]

Hi @rdlc1

Could you please enrich your post with an example of what your input data looks like, especially which date type and format?

I’m thinking about a date time shift to create a lower and upper bound and then use a rule engine to determine if A is in range of B but it would help a lot to know what the actual starting point is

[rdlc1]

Hi ArjinEX, Thanks for the reply. The table above is an example. The first 3 columns are in my data table (client, Date A, date B). I want to get an output table file where if:
a. Filter is: + or - 1 month of Date A, the output table containing only 1st row info of client A.
b. Filter is: -1 year + or - 1 month of Date A, the output table contains 2nd row info of client A but not 1st row info of client B (because while it is -1 year of Date A, it is not +/- 1 month of Date A).
c. Filter is: -2 years + or - 1month of Date A, output table contains 3rd row info of client A AND 2nd row info of client B.
and so on and so forth.

Thanks, Richard

[ArjenEX]

Hi @rdlc1

That should be possible!

To start, the date as you have there needs to be in a compatible format for KNIME so I just assumed it’s all the first of the month. They have to be of type LocalDate.

Next step is to determine the difference between date B and date A by granularity of Months.

If I understand your rules correctly. The records should be kept if they difference in months is:
1,
11,12,13,
23,24,25,
etc.

To make this a bit easier to work with, I calculate the modulo of datetime diff by calculating mod(column("date&time diff"),12) in a Column Expression node (Math Node can also be used here).

This makes it easier to filter in a Rule-Based Row Filter: $mod_12$ <= 1.0 OR $mod_12$ = 11.0 => TRUE

See WF:
Filter rows based on a non-static date range (on cell A) relative to a non-static date in same row (but on cell B).knwf (29.7 KB)

Hope this helps!