Grouping Rows to Create a Value

[Unlockedata]

Hi all,

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…

I’ve tried various loops with counter generation but can’t seem to work it out.

I feel it’s a simple problem and I can’t see the wood through the trees!

Thank you for any tips and advice.

Cheers.

[ArjenEX]

Hi @Unlockedata

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.

[Unlockedata]

Thanks @ArjenEX for your quick reply. I’m having one of those days.

Does this help?

Regarding rows 9 and 11, I’m contemplating having them as a single trip when I get past the current hurdle.

SegmentRowNumber	TripLogic	value
1	TripStart	1
2	TripEnd	2
3	TripStart	1
4	TripEnd	2
5	TripStart	1
6	Row6	0
7	Row7	0
8	TripEnd	2
9	TripEnd	2
10	TripStart	1
11	Row11	0

Thanks again.

[MartinDDDD]

Hey,

I think I got what you want to do. Only way I see is recursion (but I get drawn to that way to easily so maybe someone else has a better idea…)

Here is a prototype:
segmentRows.knwf (117.5 KB)

Overview:

Maybe some narration:

1. The original data and an initial Trip Number is fed into the recursive loop
2. 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.
3. 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…

New Prototype
segmentRows.knwf (137.7 KB)

Overview:

[Unlockedata]

@MartinDDDD - sir, thank you.

I’ve been working with Recursive loops and just could get it to work. Recursive loops are not a strength of mine.

@ArjenEX - thank you for the advice too.

[MartinDDDD]

you are welcome - check my edited post for a potential non-loop alternative

[ArjenEX]

Hi @Unlockedata

To add, I would also not go with a loop route here but with the access feature in Column Expression. It only requires two nodes:

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

[Unlockedata]

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.

A beer the next time I see you. Ha!

Thank you again.

[Unlockedata]

ah, @ArjenEX that’s awesome, thank you too sir. Appreciate your time, and as per @MartinDDDD , feels like I owe you a favour, beer, etc…

Have a great weekend.