Hello all! I am hoping someone can assist, i am trying to change the starting day of the week from Sunday to Thursdays. I was trying to follow the following instruction guide from perplexity but keep getting an error where the math formula node will not see the newly created column from Extract date & time node.
I get a warning box pop up with the following message. "Invalid settings: No such column: Date
Any suggestions would be greatly appreciated as search history on changing the start of the week in knime seems limited for this specific type scenario. See below for the instructions I was following.
To create a new date column for week numbers based on a Thursday to Wednesday schedule from your existing Sunday to Saturday schedule in KNIME, you can follow these steps:
Use the “Extract Date&Time Fields” node to extract the day of the week from your existing date column.
Add a “Math Formula” node to shift the dates to align with the Thursday to Wednesday schedule:
text
$Date$ + (4 - MOD($DayOfWeek$ + 7 - 1, 7))
This formula adjusts the date to the nearest Thursday1.
3. Use another “Extract Date&Time Fields” node on the shifted date column to extract the new week number.
4. If needed, use a “Column Rename” node to rename the new week number column appropriately.
This approach will effectively recalculate your week numbers based on a Thursday to Wednesday schedule. The key is shifting the dates so that Thursday becomes the first day of the week, then extracting the week number from these adjusted dates
Hi @dmacdonald,
If I’m reading this right, it’s because the Math Formula node can only handle numbers and cannot perform any operations on dates. It cannot even see non-numeric columns which is probably why it is saying that the column $Date$ doesn’t exit.
So, your (step 2) should be to simply return a number which is the number of days to add, e.g. an “offset” column. (i.e.remove the "$Date$ + " from your Math Formula node formula). Make sure it is set to return it as an integer (tick “Convert to int”)
Then add (step 3) which is a Date&Time Shift node, and configure this to add offset “Days” to your date, using the offset column now being output by the Math Formula.
Thank you for takign the time to reply, I am still struggling to put your response into an actual solution and I suspect this is just myself having limited base knowledge.
Would it be easier to provide a sample data set and pick your brain(s) for ways to approach the problem? Ultimately, I am just tryign to have a column that populates the week number based on a Thursday to Wednesday schedule that is determined when it reads the “Schedstart” column which is simply a date and time value.
Ironically this is just a setting box for “starting day of hte week” in tableau i can adjust so for efficiency purposes i was hoping to learn how to acheive the same effect on the knime side so it’s one less step of data manipulation to be performed after the fact. IE: Do it all in knime and just display the output data with the applicable visuals. I am not sure if BI has a similar function so if I completed this on the KNime side it would not matter what visualization software I use as the calculation is already in place.
Hi @dmacdonald , yes it’s always easier to help if you can give some sample data (ideally in text form or an uploaded data file) rather than a screenshot, so we can actually make use of it.
And if you can also provide an example of the required output, based on the sample input data supplied so we can clearly see what to aim for this is highly beneficial too.
My copy / Paste came out terrible going use a screenshot. I tried using the Python Script node to assist and was able to solve the problem with a single node which was handy. Please see the code that was generated / tuned with K AI (Shout out to those guys!) I appreciate your willingness to help!
