Good afternoon everyone, how are you?
@takbb Can you help me with this case? You had created a file to perform the BACKLOG count, do you remember? However, some are showing this error, the value is different
Hi @Gabriel2020 , what’s the error?
in this case the calculated value is wrong.
It should be 2354 and it is showing 121265
Ok @Gabriel2020 , so a couple of things to keep in mind here .
The java snippet in the thread that you are referring to, as mentioned, performed a very specific task described here, and I stated its limitations along with questions that needed to be answered regarding any additional rules that might be required:
I suspect that within the limitations I stated, it is doing what it was coded to do. If it isn’t working as you needit to, it’s probably because it did not take into account ORIGIN and DESTINY.
But I thought that this all moved on with the following post…
Did this later solution not fully cover the calculations you needed? In that post you will see I said
Generally if I ditch an idea it is because I found it deficient compared with an alternative 
so I’m not sure we should be trying to start from here again unless we really need to.
Got it Talk.
So I’m going to be very honest with you, this task is extremely important. It’s one of the biggest projects I’m doing and I’ll probably get my promotion.
I was in doubt, as only a few cases presented this problem, and it is generally when they have the same beginning, for example RIO-01, RIO-02.
The construction logic is correct, I don’t know why only some cases presented this error. Can you help me please?
I think it may be because the origin and destination are not taken into account, but I remember that it had been included in the logic.
Hi @Gabriel2020, I will try to help when I understand the question. I understand it is important to you, and I wish you well with your promotion but none of that is going to change what needs to be done, nor whether I can help you. You can start by uploading some sample data. Without that I can’t even guess.
The row of data you say is wrong is the first fully readable line in the screenshot. I need to know what data comes before it to even start.
I don’t work with your data and I don’t know your data.
I don’t remember it from 2 weeks ago except in vague terms.
Clarity of requirement is everything in this profession.
If I simply try to guess at a solution, and then you say it’s wrong again, we’ll go back in circles. This is why that last thread ran to over 60 posts. It wasn’t because the requirement was difficult, it is because you described it as if we would naturally understand your data. But you need to assume I know nothing about your data, because I don’t (as I think I wrote before!).
Back to my question… did the solution supplied in the other post, which was additionally refined by @iCFO here not calculate this correctly?:
1 Like
Incidentally, I just looked back at the previous workflows and the screenshot you have uploaded that is “wrong” doesn’t appear to match the data column format in any of them that I have looked at, so you really need to help me here if I am to have any chance of helping you…
Got the points, I’m sending the flow to you.
Come on, the BACKLOG calculation is simple.
Shipping Volume - Receiving Capacity.
Performing this calculation we have the BACKLOG for that day. However, the BACKLOG has to be calculated over the days.
Therefore, let us assume the following scenario.
10/24:
Shipping Volume = 10,000
Receiving Capacity = 9,000
BACKLOG = 1,000
10/25: Shipping Volume = 10,000 + BACKLOG (1,000) = 11,000
Receiving Capacity = 9,000
BACKLOG = 2,000
And this is how he does it, it is important that he does it by “group” considering Origin and Destination.
KNIME_projectdwq.knwf (153.4 KB)
Just one more point, if the number of this subtraction is negative, it should return 0. So that it does not impact the next day’s calculation.
I apologize for the confusion, is it clearer?
Ok, have you tried moving the loop end node, so that the backlog is calculated within the group ?
Does this then work?
3 Likes
It worked lol
I need to understand more about the functionality of this node.
Thanks again
Hi @Gabriel2020 , I’m glad to hear that works, and thank you for marking the solution.
Reiterating what I said earlier, I needed the workflow and data to be able to help. There was no way I could give a solution until you had uploaded the data and workflow that you were using… in post#9, as I had no idea how you had attached the java snippet from the other thread to the workflow from the subsequent thread. So maybe next time you could lead with that and I might be able to help you at post #2 instead of post #11 
In terms of how that java snippet works… let’s do a walk through…
It is possibly slightly easier to understand if you expand the system variables part of the code as it describes which column in your data is referenced by the variables such as c_Shoppingvolume which is your “Shipping volume” column on the data table. It also shows you which variables will be used as the output columns. So you can see that anything written to the variable out_BACKLOG will become the value of BACKLOG in the output. This section is automatically generated by KNIME when you add a “column” to the code below by selecting (double-clicking) it from the panel on the left. It does similar for flow variables selected from the other panel.
// Fields for input columns
/** Input column: "Receiving capacity" */
public Integer c_Receivingcapacity;
/** Input column: "Shipping volume" */
public Integer c_Shippingvolume;
// Fields for output columns
/** Output column: "BACKLOG" */
public Integer out_BACKLOG;
The section //Your custom variables defines any variables, and can also be used to define anything that will be executed / initialised ONCE for the entire processing of the table. In this case backlog and currentValue will both be set to 0 once at the start of processing of the table when the Java Snippet is executed.
// Your custom variables:
int backlog=0;
int currentValue=0;
The subsequent section marked /// Enter your code here is the code that is invoked for each row that is being processed. Looking at the code now, I see I could have included the int currentValue=0; line here instead, as currentValue is calculated for each row anyway. You can define variables here if you wish. Anything included in this section is executed for every row.
// Enter your code here:
currentValue=c_Shippingvolume - c_Receivingcapacity + backlog;
if (currentValue <= 0) currentValue = 0; /* if we go below zero, set to zero */
backlog=currentValue; /* store current backlog for next row to process */
out_BACKLOG = backlog;
So this section simply calculates the currentValue for the row which is:
currentValue=c_Shippingvolume - c_Receivingcapacity + backlog;
Shipping Volume - Receiving Capacity + backlog that has been “remembered” from the previous row’s calculation (starting at 0 for the first row).
The example you gave here is handled by that line of code
i.e.
Backlog = Shipping Volume - Receiving Capacity + Previous Backlog
For 10/24:
Backlog = 10,000 - 9,000 + 0
= 1,000
For 10/25:
Backlog = 10,000 - 9,000 + 1,000
= 2,000
Then the following line handles your other rule:
if (currentValue <= 0) currentValue = 0; /* if we go below zero, set to zero */
The calculated backlog to be used by the next row when it is processed is retained to make it available to the next row by placing the value into the “currentVale” variable, which as I mentioned earlier only gets initialised once at beginning of processing for the entire table
backlog=currentValue; /* store current backlog for next row to process */
Finally, the calculated backlog for this row (the same value we are carrying forward to the next row) is returned to the output data table for this row, by
out_BACKLOG = backlog;
So, that piece of code will run through the entire data table and cumulatively calculate the backlogs. You will see that there is no code in there to do the groupings, and whilst code could be added to store the previous ORIG and DEST values and then determine if they have changed, I chose to take a slightly simpler approach instead, which was to use the Group Loop which then divides your data set up into the respective groups using ORIG and DEST (and additionally, with @ICFO’s help, also included the dates as the part of the groupings - because I never understood that bit )
By putting this snippet inside the group, it is then able to execute just against each group rather than your entire data, and so you then get your calculations based on the groupings, which is what you needed.
I hope that helps with the understanding of the snippet.
3 Likes
Good catch on moving the loop end. @takbb That was also a great explanation of the JavaSnippet code and functionality. Very helpful for me as well.
2 Likes
Can you help me with one point, please? The calculation is similar to the previous one, which is why I decided to open the question here.
I need to perform a BACKLOG + FORECAST calculation.
Using that same metric of being the sum of the previous day + the day’s forecast. Previously he did less, now he will add up.
KNIME_projectda.knwf (4.9 KB)
Takbb I’m forwarding the base but with more data
KNIME_projectda32.knwf (48.4 KB)
Hi @Gabriel2020 , as your uploaded data tables contain only strings, rather than numeric types, I’ll assume that in your real data they are Integer values.
I struggled also to work out your calculations, so I’m putting here the basic outline for what you need to do. Sorry if I’m just being stupid here, but I couldn’t get it.
If you can explain how you got 36678 as the result from the numbers shown in that table, then I can help you adjust the calculation, but you should have all the info you need I think to be able to do it.
I’m also going to assume that this is an extension of the other workflow, so you should create a java snippet that follows the java snippet already in the existing workflow. (We could put it all in the same snippet, but then that just complicates it, so I’m going to suggest leaving it separate), but make sure it goes inside the loop.
Your java snippet should look like something this:
// Your custom variables:
int currentValue=0;
// Enter your code here:
currentValue += c_BACKLOG + c_Forecast; /* adjust this calc if it isn't quite right!! */
out_ForecastBACKLOG = currentValue;
NB The calculation I have here is clearly incorrect (because it doesn’t match your figures, this is where you will need to place the correct calculation)
Once you have the correct calculation, if you place this INSIDE the group loop, then the groupings for Orig and Dest should work just as they do for the other snippet.
1 Like
Good morning everything is fine ?
I’m giving a clearer example, it was my excuse. In red I indicated the number that should be reached, and in yellow what my considerations were to reach that number.
KNIME_projectda12.knwf (4.9 KB)
Ah ok, so it is forecast plus previous row’s backlog. I understand now what you were saying.
In that case, the java snippet can be written like this:
// Your custom variables:
int previousBacklog=0;
// Enter your code here:
// calculate the current forecast plus backlog from previous row
int forecastPlusBackLog = previousBacklog + c_Forecast;
// store the current BACKLOG value into previousBacklog variable to be picked up
// next row
previousBacklog = c_BACKLOG;
// output the calculated forecast + backlog
out_ForecastBACKLOG = forecastPlusBackLog;
So it first calculates the forecast plus previous backlog, and then stores away the current backlog into the variable to be picked up on processing the next row.
KNIME_projectda12 - snippet.knwf (9.2 KB)
again, putting this snippet inside the group loop will allow it to act just on the required groupings
1 Like
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.