Round down distance travelled

Question

Hi everyone

I have x2 tables. One contains names and distance they have travelled in km to one decimal place, e.g. 5.6 and the other contains latitude, longitude and distance relative to the destination.

I've used a Join tool to match on distance so I can assign someone with a latitude and longitude based on the distance they have travelled.

This is great when there is a match, but there are records where there is no corresponding latitude and longitude data.

Is there a way in Alteryx Designer to round down to the nearest available distance in the latitude and longitude data where there is no match?

Many thanks,

KA

danilang · Answer

Hi @arundaka02

Since your path is along a route and not as the crow flies, the spatial method I suggested won't work.  In a non-spatial solution, I would avoid using a regular Join at all for this problem.

Convert your steps to Miles Travelled and sum those by Pod and Date.  Use an Append Fields to do a cross join of all the input records with the route.  Calculate the absolute value of the difference between the user distance and every route marker.  Sort by Date, Pod and DistanceFromMarker and then use a Sample tool to extract the first record of every group.  This will give you the closest mile marker to each user distance.  After this you can convert to KMs.

This method also has the benefit of not rounding until after you've extracted the final records.  By rounding before, your calculations can be off an entire kilometer if the route marker happens to be rounded up and the user distance marker happens to be rounded down

This method will work in an acceptable amount of time (0.3 seconds on my machine) unless you have millions of user responses.  If this is the case, the run time will increase, but it will give you an answer.

Dan

Example KA Distance Issue-AppendFields.yxzp

MikeD · Answer

I agree with the commenter who recommended multiplying by 10 and then forcing that value to an integer.  That should be a more safe join.  But, you can also round down or up by forcing your distance value to an integer but first adding or subtracting 0.5 to/from it.  You do this in a formula tool, and set the data type for the output as Integer.

Example:  Round Down 5.6 km >>>>   5.6 - 0.5 = 5.1  Integer(5.1) = 5

Example2:  Round Up  5.6 km >>>>   5.6 + 0.5 = 6.1  Integer(6.1) = 6

arundaka02 · Answer

Hi @danilang

I'm not familiar with the spatial tools in Alteryx, yet! However, please could you help me try this? I've attached my packaged Alteryx workflow.

Essentially, I'm using a Join tool to match a pod's distance to a latitude and longitude coordinate based on rounded distance travelled. It's great when there is a match (J output anchor in the Join tool), but when there isn't, I can't plot the pod's distance on my route map in Power BI accurately as their distance travelled won't be reflected (L output anchor in the Join tool).

Many thanks,

KA

Example KA Distance Issue.yxzp