Forum Discussion
Equitable distribution by shorter distance - Geolocation
Appreciable all I require your support for the following, I need to assign clients to the closest manager by location, but these assignments should be distributed evenly, if the closest manager is a...
mmhhhh ... as far as I remember the code makes use of the list of customer ids only to associates the various managers. Each customer id is only treated once, and the list is not manipulated.
Have you checked that the duplicates are not already in your original list?
Could you just provide the list of IDs you use?
Yes.. you'r correct, one client has only one location and will be asigned to only one manager
Here's my Test Dataset https://we.tl/t-HJUVuUihTZ
Also If we asign clients in blocks to each manager, we have the problem that some clients will not be assigned to their closest manager. Using your code (thanks for it) i made changes trying to reduce this issue sorting the whole table first
disTabLoc2 = (loc as text)=> let
tabLoc = clTab{[Location=loc]}[clLoc],
//Distances Matrix
ac=List.Accumulate(
manTab{[Location=loc]}[manLoc][Manager],
tabLoc,
(s,c)=>Table.AddColumn(s, c,
each distance([latitude],[longitude],
manTab{[Location=loc]}[manLoc]{[Manager=c]}[latitude],
manTab{[Location=loc]}[manLoc]{[Manager=c]}[longitude]))),
// Get Manager ordered by shortest Distances
cols= Table.Sort(
Table.AddColumn(manTab{[Location=loc]}[manLoc], "Min Dis",each List.Min(Table.Column(ac,_[Manager])))
,"Min Dis")[Manager],
size=Number.RoundUp(Table.RowCount(ac)/List.Count(cols)),
//Sort the Table by Manager Ordered and get ID
Ids = Table.Sort(ac,cols)[ID],
// Asign Manager
Loop = List.Accumulate(cols,
[row=0,
IdsbyMan=List.Range(Ids,row*size,size),
Man=List.Repeat({cols{row}},List.Count(IdsbyMan)),
Final = Table.FromColumns({IdsbyMan,Man},{"ID","Manager"})
]
,(s,c)=>
[row=s[row]+1,
IdsbyMan=List.Range(Ids,row*size,size),
Man=List.Repeat({cols{row}},List.Count(IdsbyMan)),
Final = s[Final]&Table.FromColumns({IdsbyMan,Man},{"ID","Manager"})
]
)
in Loop[Final]