I’ve been working on a problem for far too long and need some assistance please. I can’t find what I’m looking for on the forums, and frankly, I’ve been scratching my head for too long.
Below is an example of what I’m trying to achieve…
You can do this in a few different ways but could you please share the first table in a workable format so that people don’t have to manually re-create it, that will increase your chance of getting help greatly
Edit: is it expected behavior that row number 9 with only a TripEnd should be considered a standalone trip? That will complicate the logic a bit.
The original data and an initial Trip Number is fed into the recursive loop
we split off the top row and add the current row number to it. Second Expression node checks if TripLogic in the current row is “TripEnd” and if so the new “firstTripNo” column is current number + 1, otherwhise it stays at the current number.
this way the trip number remains the same unless the previous row was TripEnd
Edit - have found a potential different solution without any loop - just not sure if it captures all scenarios.
From the data I see that TripEnd should always be there, which may not be the case for trip start.
So I filter for TripEnd, assign an index, join it back in, use Missing Value filter to set the value to next rows value… that leaves only the last rows as missing. Then I use Expressions to assin max column value + 1 for any value that is still missing…
The below code basically checks whenever a new trip should start based on the values of the TripLogic and then assigns a consecutive number accordingly.
Note, you can write it shorter with nested statements but this is easier to understand.
if (typeof globalCounter === 'undefined') {
globalCounter = 0;
}
var currentTripLogic = column("TripLogic");
var previousTripLogic = column("TripLogic", -1);
if (currentTripLogic.equals("TripStart")) {
globalCounter += 1
} else if (currentTripLogic.equals("TripEnd") && previousTripLogic.equals("TripEnd")) {
globalCounter += 1
} else {
null;
}
Just make sure the enable the Access feature.
The Missing Value node completes fills in the blanks on the TripNumbers
I’ve been on this for hours - you’ve thrown out two solutions in minutes. I owe you one - feels as though I won’t be able to repay you in the Knime world though.