Calculate start and end date of a week from a given date.

KNIME Analytics Platform
#1

Hello you all,
I am quite new to Knime after having done some experiments I would like to create my first workflow. My task is the filling a table with the values from a given csv file (sales data). The data has product numbers, day of sales (date) and value in USD and Items sold. I have to generate some values which are not within the file. Also I need to provide for each row the information from when the data set is valid.
For example the data set is from week 13 whcih starts at 23.03. and ends 29.03. However this information is not provided and needs to be calculated from the date (day of sales)

Day of sales … Date from Date to
25.03.2020 23.03.2020 29.03.2020
28.03.2020 23.03.2020 29.03.2020

and so on…

Is there a knode I can use for the calculation? How can I add a column to a table with a constant value in each row?

Thank you very much for help.

Dates – Calculate week number based on date
#2

hello @MatthiasL,

welcome to KNIME.

You might want to use first this node to calculate the day of the week using https://nodepit.com/node/org.knime.time.node.extract.datetime.ExtractDateTimeFieldsNodeFactory
Using this weekday you can calculate both the first and last day of that week.

1 Like
#3

Hi,

For the constant value in each row use https://nodepit.com/node/org.knime.base.node.preproc.constantvalue.ConstantValueColumnNodeFactory

#4

Hi @MatthiasL,

from what I understand you have multiple objectives as you wrote “have to generate some values which are not within the file.”. Though, you want to first calculate the number of the week based on a date.

The Extract Date&Time Fields node is a perfect match here.

Here is an example workflow based on your data.

Best
Mike

1 Like
#5

Thank you very much for your help, but this what I already tried. Waht I need is that I need to calculate the start date of a week and the end date of a week based on either a date in that week or from the weeknumber.
So basically I have to go the other way around as suggested.

#6

Everyone else has already given you the basic pieces. It just takes a little creativity to combine them to get what you want.

This is an approach that gets you week boundaries (defined as Sunday to Sunday):

  • Make sure your date column is a Date&Time type.
  • Use the Extract Date&Time Fields node to grab the day of the week (number)
  • Do some math to calculate week start and week end:
    • Calculate number of days to subtract to get week start
    • Calculate number of days to add to get week end
    • Convert both of these to durations
    • Use Date&Time Shift node to generate week start and week end dates
2 Likes
#7

Thank you very much for your help. With your help I solved the problem.

1 Like
#8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.