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.

Calculating distance between segments / stages in a journey

arundaka02
8 - Asteroid

Hi everyone

 

I have latitude and longitude data including distance travelled depicting a journey from London to Edinburgh (attached). In between the journey are destinations (example row 10 there is a destination named ML).

 

The distance travelled column is a sum of the distance travelled from the start (row 2). 

 

I was wondering how I might be able to determine the distance travelled between each destination, so the distance travelled 'resets' and starts again from each destination, almost in stages?

 

Fairly new to Alteryx and was thinking:

  • The Sample tool could be configured to take rows between a range? Or
  • Maybe the multi row formula tool could be used?
  • I'm aware there are also spatial analytical tools which could be used, but I've never used those before!

 

Thanks,

KA

2 REPLIES 2
AngelosPachis
16 - Nebula

Hi @arundaka02 ,

 

I think the multi-row formula tool might be your friend here. The expression I used was the following : 

IF Contains([Row-1:stage],"Destination") THEN [distance (mi)]-[Row-1:distance (mi)]

//If the previous row of the field stage contains the
//word Destination, restart the calc

ELSE ([distance (mi)]-[Row-1:distance (mi)])+[Row-1:distance between dest (mi)]

//Else estimate the distance per destination

ENDIF

 

and it seems to work fine.

 

AngelosPachis_0-1610903232151.png

 

Let me know if that works for you.

 

Cheers,

 

Angelos

 

MikeD
8 - Asteroid

To compute the distance between two lat-long points, you want the Haversine Formula (https://en.wikipedia.org/wiki/Haversine_formula).  I find it easier to compute all the component parts, and then to compute the final equation, since it has many terms.  Note that this formula gives you the distance as if you were flying directly from one lat-long to another, not along an actual ground route.  But, once you have it coded in the Formula tool, you can add more points to your route to better approximate the ground route.

Labels