# Summarize or split quantities based on weeks

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.

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.

1 Like

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?

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.

1 Like

Sorry that I missed the second part of your question.

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

1 Like

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