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 
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:
- Use a Create Date&Time Range creating 1 row with start=current execution time followed by Date&Time Shift to subtract 2 months from the current execution time
- Change the day to 1st, e.g. using String Manipulation + String to Date&Time
- Add this “1st day of the month - 2 date” to the original table using Cross Joiner
- Use a Rule Engine node to check if the date column is larger than the column with the calculated date
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