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:
- the year-month in column A are +/- 1 month of year-month in column B
- year-month in column A is -1 year +/- 1 month in column B.
Any insight on how to tackle this (nodes, workflow) is appreciated.
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
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.
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:
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
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!