Loop through segments in order to flag by minimum date

[ionut_daniel]

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!

[pedrodrfaria]

Hi @ionut_daniel 

You can do something similar to this:

Creating a function to replace the hours digit if it is more than 7 with 7. I attached the workflow so you can take a look at it.

Formula used:

if tonumber(Left([Flag],1)) > 7 then replace([Flag],Left([Flag],1),'7') else [Flag] endif

Please let us know if you were looking to do something else or if this is the solution you were looking for.

Pedro.

[ionut_daniel]

Thank you for your response, but what I have attached is a sample and has only 6AM,7AM and 8AM. The full datasource has data from 6 AM to 10PM.

What I wanted to do was a loop in order to calculate a route for a bus. For example, the bus 102 leaves from station when column I = 0 and it reaches the end of the line when column I = 15. In order to flag the route, I wanted to create a key using minimum hour (the hour when the bus left) + H + F + D, so that each route from 0 to 15  from a particular day has its own key. The purpose of this is to use it as a filter in a PowerBi dashboard.

I have multiple values for station in column I because the bus has a gps and it receives data multiple times within an station radius.

[pedrodrfaria · Accepted solution]

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 solution]

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.