I am trying to figure out how to design a workflow where the operator can enter a date for which they want data (sales, orders, shipments - anything) and the workflow will also collect the same data from that same day (not date) in the prior year without having to extract an entire years worth of data.
I have a workflow that achieves this result by extracting a years worth of data, then using a series of Date&Time Shift (this year date - 364), Extract Date&Time Fields and Joiner nodes, eliminates all of the data except the days from each year desired but it runs slow because of how much data is being processed to get to that step. I would like to only extract those days on the DB side of the workflow instead of manipulating after the DB Reader.
Is there a way to create a flow variable or something that would control a DB Row Filter for this year’s date then calculate the date shift to use as a flow variable for another DB Row Filter for the prior years data? Or anything similar to that concept? For example operator enters date of 11/19/2020 and gets data back for that day and for 11/21/2019.
To support you in answering your uqestion requires some more details.
I understand that you read your fulle database and apply some massge to the data afterwards.
Depending on your datasource it meight help to read only the relevant parts.
Maybe you can share more details and your actual workflow with us.
You are correct. I could accomplish what I am trying to do by entering both dates in a DB Row Filter with an OR condition. But that requires figuring out what date from the prior year needs to be filtered for outside of Knime (today’s date minus 364). I was just trying to figure out if there is a way to only enter today’s date and have Knime calculate the prior year date, so a user doesn’t have to calculate.
you can not transfer dates (data) from Date&TimeShift node to DB Row Filter node as you can not connect them. As @knimediger said you need to convert your dates into flow variables using Table Row to Variable or Table Column to Variable and then connect this node to DB Row Filter. Affter connection is mead you can use flow variables to build your query dynamically.
Have a look at this video to find out more about flow variables:
