Cumulative Quantity Calculation and Filtering in KNIME

[LH1212]

I am working with a dataset in KNIME that includes delivery notes (DNs), GCAS, and quantities to top up. Multiple DNs use the same pool of gcas and I need the output of which DN will have what gcas and how much is the top up QTY. After the QTY is used, then we need to remove the gcas for top up for the next DN#.
Below is the sample data, appreciate on how to approach this problems:

Delivery	Weight top up	volume top up	GCAS	Qty	KGS/CS	DM3/CS	Total Weight Top up	Total Volume Top up
1111111112	499.108	1103.874	A	40	17.566	31.2	702.64	1,248.00
1111111112	499.108	1103.874	b	48	5.385	15.8	258.48	758.40
1111111112	499.108	1103.874	c	60	15.047	33.9	902.82	2,034.00
1111111112	499.108	1103.874	d	96	5.917	17.5	568.032	1,680.00
1111111112	499.108	1103.874	e	48	6.746	16.9	323.808	811.20
1111111112	499.108	1103.874	f	80	17.302	31.2	1384.16	2,496.00
1111111112	499.108	1103.874	g	54	12.622	38.9	681.588	2,100.60
1111111112	499.108	1103.874	h	32	14.707	50	470.624	1,600.00
1111111113	5.004	4072.726	A	40	17.566	31.2	702.64	1,248.00
1111111113	5.004	4072.726	b	48	5.385	15.8	258.48	758.40
1111111113	5.004	4072.726	c	60	15.047	33.9	902.82	2,034.00
1111111113	5.004	4072.726	d	96	5.917	17.5	568.032	1,680.00
1111111113	5.004	4072.726	e	48	6.746	16.9	323.808	811.20
1111111113	5.004	4072.726	f	80	17.302	31.2	1384.16	2,496.00
1111111113	5.004	4072.726	g	54	12.622	38.9	681.588	2,100.60
1111111113	5.004	4072.726	h	32	14.707	50	470.624	1,600.00
1111111114	1305.813	1190.875	A	40	17.566	31.2	702.64	1,248.00
1111111114	1305.813	1190.875	b	48	5.385	15.8	258.48	758.40
1111111114	1305.813	1190.875	c	60	15.047	33.9	902.82	2,034.00
1111111114	1305.813	1190.875	d	96	5.917	17.5	568.032	1,680.00
1111111114	1305.813	1190.875	e	48	6.746	16.9	323.808	811.20

[MartinDDDD]

Hey there and welcome to the community,

thanks for providing a sample of input data.

Do you mind preparing an example of the desired output data?

I read through your explanation a couple of times and am not 100% clear on what you want to achieve - providing the desired output will help your potential helper to give you a solution quicker

[LH1212]

The current logic would be base on the “Required volume top up”, For example, DN 1111111112 require 1103.874, the gcas A would require 36 (36*6 = 1123.2) to top up and left 4 for the next DN top.

The desire output would be:

Delivery	Weight top up	volume top up	GCAS	Qty	KGS/CS	DM3/CS	Total Weight Top up	Total Volume Top up
1111111112	499.108	1103.874	A	36	17.566	31.2	632.376	1,123.20
1111111113	5.004	4072.726	A	4	17.566	31.2	70.264	124.80
1111111113	5.004	4072.726	b	48	5.385	15.8	258.48	758.40
1111111113	5.004	4072.726	c	60	15.047	33.9	902.82	2,034.00
1111111113	5.004	4072.726	d	67	5.917	17.5	396.439	1,172.50

[MartinDDDD]

Hmm. 36 x 6 = 216 if I am not mistaken - is it 36 x 31.2 = 1123.20?

In the first table in that 1112 GCAS A line you showed QTY = 40

is it now 36 because 1103.874 / 31.2 = 35.35 (which is rounded up 36?)

Therefore of the 40 4 can go towards serving GCAS A for 1113?

[LH1212]

yes correct, I wanted to find the QTY to fullfill up to volume top up and move the remain on the sku to 2nd #, the list of sku is the same a,b,c,d,e,f,g,h

[MartinDDDD]

ok here is my stab at this - have to admit I am still not 100% clear if this is really your full requirement - i.e. checking if there is something remaining on first GCAS and if so move that to same GCAS in next order.

There were some scenarios where there was nothing left in which case I kept the existing QTY.

I also noticed that somehow the table in your example seems to have changed in terms of values vs. what was included in your first post…

WF Overview:

Prototype:

SKUtopup.knwf (80.7 KB)