I’ve been working with some nodes. I have some data extractions that I’m doing on Knime, part of what I need to do is to take the set of data I currently have and filter only those that have a valid price for current dates.
In this case this is how the validation should work as a concept:
Take all the list of codes and verify the dates (valid from and valid to)
-Valid From is <= Start of month = TRUE
-AND Valid To is >= Start of Monht = TRUE
I want to see only those codes that are compliant with both conditions. I’ve seen that I can filter by date but it’s a static date.
Does anyone know if there’s a way to set the date as “start of month” meaning that this will be dynamic and every month the date that I should be validating against should be the current month.
As you need month to be dynamic you should use Date&Time Input node to get current time/month. After that there is really couple of possibilities to solve your problem. Rule Engine node, Column Expressions node (Create new date column based on other date columns), Date&Time- based Row Filter. For first two nodes you will write your rule and create new column based on which you can filter and last one will give you immediately what you need only you should use Flow variables for start and end time.
Thanks this is very helpful. The last part I was actually thinking to use the Date&Time-based Row filter, however what’s sstill not very clear for me is the first part.
Using the Date&Time Input how can I make it to be dynamic? For what I see on the node I can choose the start of current month, but for next month will it be automatically updated or I’ll have to go back and choose again the start of the current month?
Any help is appreciated as I’m very new with this.
What you can do is uncheck Earliest and Latest and use only Execution time under Default Value. This way you will get timestamp from which you have to extract month and then based on it define first and last day of current month. This way you will not have to update anything manually.
What I would do is define first and last day of months for whole year in internal Table (with Table Creator node) or in an external file (Excel, csv). Then when you extract month from timestamp you go in that table and check what is the first day of month and what is the last day of month. Just be careful of leap year.
Play with KNIME a bit. Maybe you will find a better solution
Thanks for your idea. I started playing with Knime and I came up with a different solution, based on the suggestions you made.
What I did is:
1 - Create a date based on the current date (“use execution time”) on the Date& Time Input. Which was saved to a variable
2 - I used the table creator to include the value of the date I had created
3 - I used the String manipulation to split the date keeping only the year and month
4 - Again I used the string manipulation to join the year/month I had from previous step and adding “01”
As what I need is to have the 01 day for each current month this approach lets me to keep the month and year dynamic and to have the static day 01 everytime I run the workflow.
As final step I included the new date as a variable using the “table column to variable” node.
