Challenge #15: Warehouse Shipped Miles

Here is a new challenge for this week, it is a continuation from last week’s warehouse distribution challenge. If you did not complete last week’s challegne don’t worry, you will not need any output from part one to complete this part. The link to the solution for last week’s challenge is HERE. I posted two solutions for last week’s challenge. The first is a solution without a macro and is in my opinion a more straight forward approach to solving the problem. I included the second macro approach because it is an excellent example of how to utilize an iterative macro.

The use case:

Based on data from last week’s warehouse distribution challenge, we want to calculate the total shipped miles per item. The products are available from 3 different warehouses, lat/lon data is provide for each warehouse and each store location.

Your goal is to find the total distance travelled as straight line miles for each item based on it being shipped from the closest warehouse.

Good luck, I hope you are having fun with these challenge and expanding your knowledge of Alteryx. Thanks to all that have provided feedback.

challenge_15_start_file.yxmd

challenge_15_solution.yxmd

Join

Spatial Analysis

Preparation

Advanced

Transform

Basic

Spatial

Accepted answers

All comments

TaraM

The solution to this exercise has been uploaded.

MarqueeCrew

@JoeM,

Not many takers on this challenge.

challenge_15_MarqueeCrew.yxmd

SeanAdams

:-) so I did it the hard way, but glad to have learned from the solution provided.

Spoiler

- joined all warehouses to all stores. At this point I had a list of stores with the products; cross-joined to the warehouses (so SxIxW rows)
- Worked out the distances for all combinations
- then filtered out all but the shortest.

The solution provided works well if every warehouse has every product, and is way more efficient because it only uses SxI rows (number of items by store) - but if different warehouses had different product sets, then an approach like mine may be needed where you actually have to explode down to the store/item/warehouse level for every item.

Anyway - learned more about the "find nearest" component from the provided solution - thank you @GeneR and @MarqueeCrew

challenge_15_SeanSolution.yxmd

NicoleJohnson

Brushing up on my (basically nonexistent) Spatial skills in advance of the Grand Prix...

Spoiler

Solution: Create Points > Find Nearest Tool (Max Distance 10000 miles) > Join Item data > Formula: Distance Traveled > Summarize: Group By Item, Sum Distance Traveled.

challenge_15_NicoleJohnson.yxmd

estherb47

challenge_15_EHB_solution.yxmd

Laurap1228

My solution was the same as the one posted.

Spoiler

challenge_15_LP.yxmd

JustinMatthews

Never needing to use spatial tools for my current job, this challenge was very insightful for me. And just simple enough

Spoiler

challenge_15_start_file with my solution.yxmd

LordNeilLord

solution

Spoiler

challenge_15_LNL.yxmd

robcarroll

My Solution. First spatial challenge

challenge_15_solution.yxmd

sagarb

Good introduction to spatial tools

challenge_15_SB_Solution.yxmd

nick_ceneviva

Solution attached

challenge_15_Ceneviva.yxmd

jamielaird

Here's my solution. I rarely use spatial tools so this is great practice.

Spoiler

challenge_15_JL.yxmd

JacobR77

Always good to have the chance to use spatial tools.

Challenge 15- Warehouse Shipped Miles.yxmd

espeigle

I had a similar solution to the one posted, just in a different order:

Spoiler

challenge_15_ES.yxmd

patrick_digan

Here is my solution

challenge_15_start_file.yxmd

luciebrett

Same as example apart from an extra select tool.

challenge_15_Warehouse Shipped Miles LB.yxmd

Natasha

Similar solution and I doubt there are many different ways to it.

Spoiler

challenge_15_NK.yxmd

MaxGiegerich

Done. Although not a big fan of the 'Find Nearest' maximum distance filter. I thought this would only go up to 999 miles (and therefore filtered stores out in the example) so have done it two ways, one not relying on this tool.

Thanks!

Weekly Challenge #15 MG.yxmd

david_elliott

First weekly challenge completed, Nice intro in to special.

Challenge 15.bmp

marcreid

Using Find/Replace

Spoiler

challenge_15_MR.yxmd

JoshKushner

Really enjoying these spatial problems. I don't have a chance to practice them with any data sets that I use.

challenge_15_start_file.yxmd

930realm

Spatial problems are the best as I am a GIS person learning Alteryx.

challenge_15_SP.yxmd

dsmdavid

Loving the spatial tools

solved#15.yxmd

samN

Pretty simple spatial

challenge_15.yxmd

Amarendra

Uploading my solution to the challenge

challenge_15_Amar.yxmd

ggruccio

Love spatial challenges!

challenge_15_gg_finish.yxmd

LandonG

My solution.

challenge_15_start_file.yxmd

philipmannering

Neater way of doing it with the Find Nearest tool. See attached.

challenge_15 - Snip.PNG

challenge_15 - Total Miles of Item Distribution.yxmd

wanjum

Solution attached.

challenge_15_solution_waqas.yxmd

jasperlch

Solution attached.

challenge_15_JL.yxmd

mceleavey

A nice introduction to spatial analytics.

Spoiler

I think mine is the same as pretty much everyone on here.

I joined the first two data-sets together on store number then created the mapping points for both streams, one for the stores and one for the warehouses. Then it was a simple matter of using the find nearest tools (with the distance spanked up to 5000 miles to be sure), creating a multiplication of distance by number of each item and summing the results by item.

challenge 15 workflow.PNG

challenge_15_solution_mceleavey.yxmd

hgupta239

Spoiler

challenge_15_start_file.yxmd

princejindal

Even I took the same hard way, until I realized I could manually set the limit of maximum distance in the find nearest tool, whose drop down limit extends only till 100.

adityan92

4th challenge completed!!!

challenge_15_solution_adityan92.yxmd

SubratDas5

My Solution

Spatial_WarehouseShippedMiles.yxmd

AdrienGohy

Spoiler

challenge_15_Adgo57.yxmd

Junya_Wang

Spoiler

Spatial is always fun

challenge_15_Junya Wang.yxmd

teruoarai

Hi Tara,

Could you please help understand why we need to calculate DistanceMiles = [DistanceMiles] * [Assigned]?

Does 47 in [Assigned] means the shipping truck will travel 47 times from the store location to the closest warehouse?

That was the only part that I was confused with.

Thanks a lot for your help.

Regards,

Teruo

WernerE

Thanks

challenge_15_start_file.yxzp

rajenderk18

Thanks for explanation.

Hatchetman

Here is my solution. Not as efficient as the provided solution, but given I have little experience with spatial data and was learning the spatial tools on the fly, I think I did pretty good and ultimately got to the same place with a few extra steps. This was a great exercise as it showed me that even with no prior background with certain data types, I could still use some judgement and available help tools to figure out a way to get there.

One thing I think could have been made a little clearer in the instructions for the challenge is that what is desired here is the total miles for all the assigned items from each store based on where they are shipped from and not just the total distance for each single item based on where it is shipped from. I was able to figure it out eventually without referencing the solution, but I had to take a hit and miss approach multiplying the item shipping distance from each warehouse, first by the required, and then by the assigned to get to the correct expected result. In effect, I backed into it with a little trial and error. It felt a bit like playing darts

challenge_15_hatchetman_solution.yxmd

mgaronson

Spoiler

Did this 2 ways. Once without the Find Nearest tool and once with the Find Nearest tool. Saved a few steps with Find Nearest.

challenge_15_Complete MGA.yxmd

chuckleswk

I don't use spatial tools a lot, so this was a nice practice!

Spoiler

challenge_15_start_file.yxmd

sanjay_shinde

Using spatial analysis for the first time and its amazing.

challenge_15_solution_sanjay_shinde.yxmd

Lucy_Hickman

This is how I would have solved for this challenge, but the 'find nearest' was updated to restrict max distance to 100 miles/ kilometers. This restriction makes this tool practically unusable when trying to find the closest warehouse across the country.

rapetter

Done

challenge_15_mysolution.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335