Assume a certain date on weekday basis (last monday was which date etc.)

[Konrad19]

Hi everybody,
right now I’m struggling with the following problem. I have a table with a column holding the weekday in string format (“monday”, “tuesday” etc.). I know intend to get last corresponding date, for example since today is monday, the input of “monday” should create the date field “2021-06-21” on the output.
My only idea would be, to convert each day into a number from the range [0-6], then subtracting this number based on the current day to calculate the date corresponding to the weekday. However maybe there is some more pleasant solution I’m overlooking right now.

[ipazin]

Hello @Konrad19,

and welcome to KNIME Community!

Just to check if I got it right. For Monday you want 2021-06-21, for Tuesday you need 2021-06-15, for Wednesday you need 2021-06-16 and so on…? And you got Mon-Fri in a table? And just for completeness I assume you are to run this process on a daily basis?

Br,
Ivan

[bruno29a]

Hi @Konrad19 , welcome to the forum. Just adding to the clarifications that @ipazin is asking, would you go more than a week? If so, how do you distinguish between last Tuesday and the Tuesday from the week before? Or today’s Monday from last week’s Monday?

[Konrad19]

Thanks a lot for your qlick reply. Your assumption of what I’ve ment is quite right, although I actually got Mon-Sun in a table. I intend to run this process on a daily basis, so 7 days from now I would expect 2021-06-21 for monday, 2021-06-15 for tuesday and so on.

[Konrad19]

Hi, thanks for your reply, it just concerns the last seven days, so on this level of the process I’m asking about, there is no need to distinguish between ast Tuesday and the Tuesday from the week before.

[ipazin]

Hello @Konrad19,

you can use Create Date&Time Range with following configuration to generate last 7 dates:

Then you can use Extract Date&Time Fields to get corresponding days of the week.

Does this solve it?

Br,
Ivan

[bruno29a]

Hi @Konrad19 , the solution proposed by @ipazin is correct.

Since you will need to run this on a daily basis, the node Create Date&Time Range allows you to run without having to adjust today’s day with the “Use execution date&time” option that takes care of this, and it can be configured to give you the last 7 days from today.

And as @ipazin mentioned, use the Extract Date&Time Fields to get the days of the week.

You can then JOIN this with your table on the weekday column to link the dates to your data.

Here’s a little workflow I put together to demonstrate this:

Sample data looks like this:

The idea is to assign the dates to these weekdays. And here is the result:

And you run this tomorrow, it will give you the proper results automatically.

Here’s the workflow: Assume a certain date on weekday basis.knwf (11.3 KB)

[Konrad19]

Thanks a lot @ipazin and @bruno29a for explaining and giving me the example process, that’s helping me a lot!