Forum Discussion
Equitable distribution by shorter distance - Geolocation
to deepen the performance aspects (certainly the use of list.accumulate is not highly recommended), it is necessary to know:
the number of locations?
for each location how many managers? 10 to 15 managers, for example
for each location how many customers? 100 to 250 customers, for example
PS
What data did you test the code on and how long did it take?
what is a time you reasonably expect?
Anonymous
Searching on the net, i found this article,
I made the change in the code and it takes less time than before.
The data set I used for the test is about 300 records, the data is in the code in my previous answer
In my first test it took me more than 5 minutes for only 200 records...
Now I can process 1500 in 6 minutes.
I am still looking for improvements because in my final data set there are 33,000 records (every month it's increase) divided in 20 places each one has between 1 and 4 managers having each one on average 1,200 customers; I also have to do other transformations to get the final result, so I need the fastest code , plus I'm not a very patient person 😄
Hi Dihros
my code, although more concise than yours, it is no more efficient.
try to implement the following scheme if it is acceptable as a solution.
Once the distances matrix has been obtained, having the managers as columns and the distances from the various customers as values, sort by increasing values on the first column (man1, for example) and take the first [customers / man].
remove the first column and the first [customers / man] from the matrix and continue to the last column.
This scheme has the advantage that heavy control operations are performed by functions (therefore with more efficient algorithms and probably faster because they are made in a lower level language)a script that deals with a table of 1700 customers on 8 managers in 4 locations, in a short time
per scaricarli, fai click sul seguente link e segui le istruzioni.
manTab and clTab are the data tables.
ClientsDistribution the query that produce (using the custom functions provided) the resulting distribution table.
####edit#####
I didn't pay attention to the fact that there were more than ten managers in the data and so I used a formula
Text.End (c, 1) which takes only the last character of the string, so "Manager 1" and "Manager 11" get mixed up.
The following is the correct (and also much simpler) formulation of the function:Thank you for your help which I appreciate very much, I was very surprised to see the speed of your code,
While understanding the code I tested it with my test records and noticed that some customer numbers are repeated
It seems to me that they repeat when they have the same distance
I'll keep checking the code,
Thank you so much
Greetings