1. Maybe this image gives explanation why the first idea won't work (if I remember correctly the second solution relies on that if you calculate . + 1 it will be .
![jthi_2-1640633086678.png jthi_2-1640633086678.png](https://community.jmp.com/t5/image/serverpage/image-id/38666i392AC41C78DB1C1F/image-size/medium?v=v2&px=400)
I did take the different comparisons from the If statement and just create formulas out of them so it is easier to see what returns True and when.
You could most likely get the first solution working by modifying the comparisons a bit ( Is Missing(:StartEnd) & Lag(:StartEnd) != "End" | :StartEnd == "End", val += 1). But with this change you will have to make small change to second comparison also (add Row() != Col Max(Row()) check).
2. Idea for Row() == Col Max(Row()) & :StartEnd == "Start" is just to to check if we are on last row of the table and :StartEnd value is "Start" we won't set the value as 1 based on previous if statement. Row() will tell you row number and Col Max(Row()) will return you maximum row number in the datatable. Might be a bit unnecessary check because it is last row of table (like the one after it with Start/End), but as it was in the results column, I did implement them.
The general idea with the last if-else was following:
1. Lag(:StartEnd) == "End" -> this is general "stop" after End is countered
2. Row() == Col Max(Row()) & :StartEnd == "Start" -> to prevent returning 1 on last row if it is Start
3. :StartEnd == "Start" & Lag(:StartEnd, -1) == "End" -> to prevent returning 1 and 2 if there are no rows between Start and End
You can use just one if with multiple comparisons by moving some of the comparisons around a bit (this seemed to return same result as Req1):
If(
Row() == 1, val = .,
Row() != Col Max(Row()) & :StartEnd == "Start" & Lag(:StartEnd, -1) != "End", val = 1,
Is Missing(:StartEnd) & Lag(:StartEnd) != "End" | :StartEnd == "End", val += 1,
val =.
)
Hopefully the explanation isn't too messy
-Jarmo