Summarize or split quantities based on weeks

[gentile]

Dear KNIME community,

I have a list of orders and need to summarize all quantities that are in the same week and split all those quantities that come with a period of time.

This is the data that I have:

material no.	order no.	order week	order qty
material1	1	2022-4	1
		2022-4	2
		2022-5	3
		2022-6	4
material1	3	2022-7	9
		2022-7	8
material1	5	2022-5	120
		2022-6	150
material2	6	2022-7	1
		2022-8	2
		2022-4	3
		2022-27 - 2022-30	144
		2022-31 - 2022-34	288
		2022-35 - 2022-38	432
		2022-39 - 2022-42	432

This is how the result should look like:

material no.	order no.	order week	order qty
material1	1	2022-4	3
		2022-5	3
		2022-6	4
material1	3	2022-7	17
material1	5	2022-5	120
		2022-6	150
material2	6	2022-7	1
		2022-8	2
		2022-4	3
		2022-27	36
		2022-28	36
		2022-29	36
		2022-30	36
		2022-31	72
		2022-32	72
		2022-33	72
		2022-34	72
		2022-35	108
		2022-36	108
		2022-37	108
		2022-38	108
		2022-39	108
		2022-40	108
		2022-41	108
		2022-42	108

I am struggling to find a way how to do this.
Any ideas?

Thanks.

[Thyme]

You can use the GroupBy node with “material no.”, “order no.” and “order week” as group columns and “order qty” as aggregation column, using “Sum”.
If you set the column naming to “Keep original names” you won’t have to rename the columns afterwards.

[gentile]

Cool, I love simple and elegant solutions like this! Thanks.
I was just testing “Moving Aggregation”, but that was not the right one.

Any idea on the splitting for multiple weeks?

[gentile]

For anyone interested, I managed the split. And this is how I did:

I used the Row Splitter to separate the 4-week periods from the single weeks. Then I added a Constant Value Column with “4” to be used in the One Row to Many node, so that each line will be repeated 4 times. The Group Loop Start was used to identify one order and one period (always 4 rows). Those rows received a counter starting from “0”. I used the Column Expressions node to separate the first week showing up in the string and adding the counter to it. Finally, Concatenate with the second output of the Row Splitter node which was the single weeks.

[Thyme]

Sorry that I missed the second part of your question.

Great to see you managed to solve that problem on your own!