Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Loop through segments in order to flag by minimum date

ionut_daniel
5 - Atom

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)

 

ionut_daniel_0-1609860653219.png

 

Any ideas would be much appreciated!

 

 

4 REPLIES 4
pedrodrfaria
13 - Pulsar

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

 

pedrodrfaria_1-1609883106541.png

 

 

pedrodrfaria_0-1609883094900.png

 

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
5 - Atom

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
13 - Pulsar

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
17 - Castor
17 - Castor

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.

 

DavidP_0-1609939722936.png

 

Labels