How to calculate cumulated value with conditions

[Reader91]

Hi,
I need your help with this excel file to find the exact usage of winter (W) and summer (S) tires for each car plate

Key points of this file:
-It’s a list of records of all the times that cars went to a garage to change the tires (jobs)

• Each plate has different jobs, they can be changing from S to W tires, W to S, S to S or W to W;
• The first job of each plate is always S to W (with a NEW W tyre fitted)
• From the second job, the tyre fitted can be from “STORAGE” or “NEW"
• In each record, the number of kms represent the kms registered on the car when it arrived at the garage to change tyres

Starting from this, I would like to calculate for each plate:

• the amount of km from a job to another, with no distinction between type of tyre
• the total usage of each tyre set before it is changed with a new one of the same type (it means every time we have a new
  I think the output should be something like this

I’ve already tried with a group loop and lag column and the first part is quite easy but the second one is driving me crazy.

example.xlsx (845.7 KB)

[tommy]

hi @Reader91 and Welcome to KNIME.
that’s a nice exercise to start with as KNIME is perfect for it.
You’re right, Group Loop Start and Lag Column is the beginning. Afterwards you may split Winter and Summer and perform a moving sum on each.
Please have a look at the Workflow on KNIME hub…

Z_026_moving_sum – tommy

Hope, that helps, greetz,
Tommy

[Reader91]

Hi @tommy,
thank you so much for your help.
I was looking now at my first post and I saw there is a part missing (that I’m adding in bold)

"Starting from this, I would like to calculate for each plate:

• the amount of km from a job to another, with no distinction between type of tyre
• the total usage of each tyre set before it is changed with a new one of the same type (it means every time we have a new TYRE FITTED THE COUNTER RETURN TO 0 "

To better understand what I mean, I’m using only one plate as example; the sums from the first three jobs are correct because in the third job the car is using a set of tyres from the storage (as indicated in the second row), in the forth and fifth job the car is using a brand new set of tyres (as indicated in row n.3 and 4 respectively) and it means that for both the Km done with that set can’t be cumulated with the previous one and it has to restart from 0 ( because is made with a different set of tyre).
What I need is 2494 in the third row (cumulating row n.1 and 3), in row n.4 I need to find 4050 (not 6544) and in n.5 2204 (not 8748), both from a new set of tyres

I think this flow is very close from what I’m looking for (and this is a huge step thank to u).

Many thanks

[tommy]

hi @Reader91,

I see - it’s a minor change, however, I suggest to switch to a Java code for the conditional moving sum.
Please have another look on KNIME hub.
At least your example shows the right cumulative km now.

br, Thomas

[Reader91]

What I need is a mix of it.
In the first job, at 150 km, I’m unfitting summer tyres (150 km in the summer column is correct) and fitting a new set of winter tyres
In the second job, at 4556 km, I’m unfitting winter tyres (meaning that with the W tyres I’ve made 4406km)and fitting a set of summer from the storage, the one I’ve used from Km 0 to Km 150
In the third, at 6900 km, I’m unfitting summer tyres from storage (meaning that with that set of tyres I’ve made the first 150km + 2344 km) and I’m fitting a brand new set of summer tyres-> In this line, in the summer_km column, I’m expecting to find 2494.
In the forth job, at 10950, I’m unfitting a set of S tyres that was new in the in the third job (and with this set I’ve only made 4050 km) and I’m fitting another new set of tyres

It means that in the row 4 and 5 the number in the summer km is correct, in the row 3 it must be the sum of row 1 and 3.

The same can happen with the W tyres

[tommy]

Hi @Reader91,
ok, I just updated the workflow and reorganized the Java code.
So please have a look. The logic of summing and resetting should now be correct.
Br, Tommy

[Reader91]

If I need to add another variable related to another type of tyres, I only need a row in the javascript code?

[tommy]

Yes, that would be done in the Javascript Snippet node.
With your explanation I cannot imagine what would change, depends if you need another two cumulative sum columns (summer/winter) for the new variable.

[Reader91]

Oh no, is just related to the 4 season tyres.

Looking at the code I think I need to add the same rows but changing with “4 Season”

[tommy]

ok, you would need one row in addition for each section in the Javascript code.
Except the middle section…

int summerkm = 0;
int winterkm = 0;
int fourseasonkm = 0;

out_Winter_km = null;
out_Summer_km = null;
out_4Season_km = null;

if(c_TyreOut.equals("Summer")) {
	summerkm += c_kmdone;
	out_Summer_km = summerkm;
}
if(c_TyreOut.equals("Winter")) {
	winterkm += c_kmdone;
	out_Winter_km = winterkm;
}
if(c_TyreOut.equals("4 Season")) {
	fourseasonkm += c_kmdone;
	out_4Season_km = winterkm;
}

if(c_TyreIn.equals("Summer") && c_TyreInType.equals("New")) summerkm = 0;
if(c_TyreIn.equals("Winter") && c_TyreInType.equals("New")) winterkm = 0;
if(c_TyreIn.equals("4 Season") && c_TyreInType.equals("New")) fourseasonkm = 0;

And, of course, you need another output column.

[Reader91]

Tommy,
thank you for your valuable support.
I have one last question; is it possible to add another column at the end that highlights the last job where I’ve used a set of tyres?

Practically, in the example I’ve used before:
The first job, at 150 km, is not the last job where I’ve used the set of tyres because the garage has fitted in again in job n.3, meaning that in the column I have to find blank.
The second job, at 4556 km, is the last and only one with winter tyres, I don’t know yet if in a future job I’ll fit a new set of winter tyres or a set from the storage, meaning that in the column I have to find a reference.
The third job, at 6900, has to have the reference because is the last job made with that set of tyres I’ve fitted first in job n.1.
The forth and fifth job, need to have the reference because on both I’m using a new set of tyres and there aren’t job related to them in the past or in the future.

The reference can even be a “1” that can help me in my reporting on excel

BR

[tommy]

you can have a look in the future using sorting Date DESC and a lag column.
That’s what I did for each tyre type. So if the next value is “New” or there is no future value, then the indicator is set to 1.

See the Hub Workflow. You may download it and get your minor changes done by yourself.

Thanks, Tommy

[Reader91]

You made a hell of a job.
Thanks mate.