Cumulative Quantity Calculation and Filtering in KNIME

KNIME Analytics Platform
1

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
2

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 :slight_smile:

4 Likes
3

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
4

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?

5

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

6

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:

image
image1085Ă—439 8.64 KB

Prototype:

SKUtopup.knwf (80.7 KB)

1 Like