Loop through segments in order to flag by minimum date

Question

Hello

I need an advice regarding a loop that I have to do. As you can see in the image below, I would like to loop between 0 to 15 (from column I) in order to get a result that is similar to column L (minimum hour + H + F + D). I have created a parse to extract the hours from column K.

The problem is that even if my loop starts at 7.59, I would like to keep that flag at 7, even if the hour is 8.00 (as long as it is kept between the segment of 0-15)

Any ideas would be much appreciated!

datasource.xlsx

pedrodrfaria · Accepted Answer

Hi @ionut_daniel In this case you only need to update the formula to consider the max hours, (10 in this case) and assign to what you would like to rewrite, if it is always to a specific number or if it is to the number below, see below the formula example. if tonumber(Left([Flag],1)) > 10 and tonumber(Left([Flag],1)) <9 then replace([Flag],Left([Flag],1),'10') elseif tonumber(Left([Flag],1)) > 9 tonumber(Left([Flag],1)) <8 then replace([Flag],Left([Flag],1),'9') elseif tonumber(Left([Flag],1)) > 8 tonumber(Left([Flag],1)) <7 then replace([Flag],Left([Flag],1),'8') elseif tonumber(Left([Flag],1)) > 7 tonumber(Left([Flag],1)) <6 then replace([Flag],Left([Flag],1),'7') elseif tonumber(Left([Flag],1)) > 6 tonumber(Left([Flag],1)) <5 then replace([Flag],Left([Flag],1),'6') elseif [Flag] endif This formula above replaces hours above 10 and less than 9 with 10, above 9 and less than 8 with 9, above 8 and less than 7 with 8, above 7 and less than 6 with 7 and above 6 and less than 5 with 6. So always getting the hours before or the minimum. Please let us know if you need additional help or if I misunderstood your ask. Pedro.

DavidP · Accepted Answer

Hi @ionut_daniel

Thanks for your 2nd clarification, which helped better understand what you're looking to achieve. Your data is already nicely ordered based on lastContact, but you could use a sort tool to ensure the rows are in the correct sequence. Using a multi-row formula tool, you can then create an identifier number for each leg of the entire route (0-15) - I created a new field called Route sequence. There is roughly 65 GPS points in each leg. For each Route sequence we can then easily create the unique identifier you're looking for, as shown in the attached.

I've gone 1 step further and just showed how the route can be plotted using the spatial tools. Feel free to have a look at the workflow and come back to me if you want to explore anything further.