Round down distance travelled

[arundaka02]

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

[Hollingsworth]

Two methods:

1 - I would try using the Round() function in a Formula tool.

2 - Try multiplying your distance column by 10 and then convert to an integer. Joining on integers is always going to be safer than joining on floating point numbers. You may have noticed the warning in the output to this effect.

Good luck!

[arundaka02]

Thanks @Hollingsworth it's prompted to me to find this article: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Round-Numeric-Values-with-th...

Looks like I'll have to round to miles first before converting to km but no problem. 

[danilang]

@arundaka02 

You can also try to solve this spatially using this technique 

1. Use a Trade Area tool on each of your traveler records to build trade areas the size of the distance travelled. Add a small buffer to the distance to catch points that fall just outside the trade area.   

2. Use a Spatial Match tool to find the intersection of the trade areas with the targets

3. Use the Distance tool to find the distance between the traveler and each of the targets 

4. Pick the records that are the closest to the distance travelled by the traveller

Dan

[arundaka02]

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

[MikeD]

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

[danilang]

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