How to calculate a series of dates from different accounts in the same table and see if they fall within a specified time period between each other.

KNIME Analytics Platform
1

Greetings,

Is it possible in KNIME to have it tag or label a row in a column for when two date rows occur within a 24-month period between each other?

The example data looks like this:

Account | Date | Meets 24-month Criteria
Account 1 | 1/1/2019 |
Account 1 | 12/7/2019 |
Account 1 | 8/9/2019 |
Account 1 | 5/1/2017 |
Account 1 | 1/1/2016 |

Account | Date | Meets 24-month Criteria
Account 2 | 1/1/2019 |
Account 2 | 12/7/2011 |
Account 2 | 8/9/2014 |
Account 2 | 5/1/2007 |
Account 2 | 1/1/2003 |

Account | Date | Meets 24-month Criteria
Account 3 | 1/1/2019 |
Account 3 | 8/7/2014 |
Account 3 | 8/9/2019 |
Account 3 | 5/1/2007 |
Account 3 | 1/1/2007 |

It presents this:

Account | Date | Meets 24-month Criteria
Account 1 | 1/1/2019 | Y
Account 1 | 12/7/2019 | Y
Account 1 | 8/9/2019 | Y
Account 1 | 5/1/2017 | Y
Account 1 | 1/1/2016 | Y

Account | Date | Meets 24-month Criteria
Account 2 | 1/1/2019 | N
Account 2 | 12/7/2011 | N
Account 2 | 8/9/2014 | N
Account 2 | 5/1/2007 | N
Account 2 | 1/1/2003 | N

Account | Date | Meets 24-month Criteria
Account 3 | 1/1/2019 | Y
Account 3 | 8/7/2014 | N
Account 3 | 8/9/2019 | Y
Account 3 | 5/1/2007 | Y
Account 3 | 1/1/2007 | Y

Thank you, and look forward to your reply.

Signed,
John

2

Hi,

You can use the Lag Column node then Date&Time Difference and finally the Rule Engine node.

:blush:

3

Hi there @jhandatx,

Not sure what is exact criteria for row to get “Y”. Is it only sufficient that within same Account there is row (doesn’t have to be previous or next) within 24 month period?

Br,
Ivan

4

Greetings Ivan,

The criteria to achieve Y is the dates within the same account have to be within 24 months of each other. So if:

Account | Date | Meets 24-month Criteria
Account 3 | 1/1/2019 | Y (Note: This is a Y because it falls within 24 months of another time within the same account)
Account 3 | 8/7/2014 | N (Note: This is a N because no other time falls within 24 months within the same account)
Account 3 | 8/9/2019 | Y (Note: This is a Y because it falls within 24 months of another time within the same account)
Account 3 | 5/1/2007 | Y (Note: This is a Y because it falls within 24 months of another time within the same account)
Account 3 | 1/1/2007 | Y (Note: This is a Y because it falls within 24 months of another time within the same account)

Hopefully, that helps explain the output request. :smile:

Signed,
John

5

That worked, thank you. :smile:

6

Hi there,

not sure what worked but glad it did :smiley:

Anyways here is workflow example you can check:
https://kni.me/w/MAYFON1Ul6mbyPxm

Br,
Ivan

7

Greetings Ivan,

After further analysis you are correct, Armin’s recommendation if I understood it’s application correctly only covers the record right above to see if it fits the criteria. There are variables with different amounts of Account rows. (Some have 2, some have 3, some have 4, some have 5, etc.) So I will dive into your workflow recommendation and test if it’ll solve my application.

Thank you again.

Signed,
John