categorize rows based on whether their date fits a period of x previous months

Hi!
I am using KNIME at work to build workflows that generate Excel reports of KPIs.
In hdbstudio, we had a way to automatically categorize, in only one step, whether a row had category “Year” or “Last period” ((creating a new column), based on whether the row’s date fits the period of the previous 2 months or not, even with the update of the source data.
Is there a way to do this in KNIME, in order to get one table with that category column automatically filled based on the rows’ dates fitting the 2 previous months or not, with source data updated every month?
Thank you from a non-data-analyst

Hi @Sara_Santos,
Welcome to the KNIME Forum! I think you will need two nodes to get what you want, but it is rather easy. With the Date&Time Difference node you can calculate the difference of a Date&Time column to the current execution time. You can choose a granularity of days, months, hours, etc. After that, you can use a Rule Engine node to check if the difference is smaller or larger than a value you define.
I hope that helps!
Alexander

1 Like

Hi, @AlexanderFillbrunn!
Thank you for answering so quickly and for the tip :slight_smile:
I need to include in the category “Last period” all the rows with date between day 1 of [current month-2] and last day of [current month-1]. I assume this means I need to compare dates based only on month and year, and not days. However, I am not finding in KNIME a way to change the dates format from dd-mm-yyyy to mm-yyyy, to then do a Date Difference and categorize the rows that have Date Difference =1 or 2 (AKA rows that have their date in one of the 2 previous months) as “Last period”. Do you know a solution to get this?
Sara

1 Like

Hi Sara,
Okay, then you will need a few extra nodes, I think. What you can do is this:

This maybe sounds a bit tedious, but you can wrap it up into a component and then reuse it whenever you need.
Kind regards,
Alexander

2 Likes

Hi, Alexander!
Thank you so much for the explanation.
I will try to do that and create a component, given we are building several workflows to produce KPI reports.
Kind regards,
Sara

2 Likes

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