Some of these O & D belong to a certain zone. Due to an incorrect registration, I need to take together all station codes from each zone, and the zone code itself. Then, I need to replace/re-distribute the individual station codes within each zone according to a pre-defined distribution. (this way, also removing the zone-code)
E.g. Zone L (code 3100) contains station L (code 3001) & Station H (code 3126). I isolate all records that contain these 3 codes in their O(rigine). Then I defined that station L should get 85% of travels and station H 15%.
I have the re-distribution on row-level (working fine), but I would need it to be based on number of travellers.
So if I now get 750.000 lines with one of the 3 given codes, I can redistribute to 112.500 lines Station H and 637.500 lines Station L.
I would need however maybe 60.000 lines Station H, as these correspond to 15% of travellers within the total of the 750.000 lines.
The idea would be either to create a mapping table (as done now for the re-distribution on row-level), or to amend the table right away (probably per zone - 21 in total).
Any suggestion on how to approach this would be appreciated.
An aside, linked to this is that my tables are ordered by product code. Thus, assigning the first x lines to 1 single station risks to assign a too big proportion of a certain product to that particular station. Is there a way to either 'shuffle' the table, or select at random lines out of the table and assign them untill they've all been given a new station code?