0 Replies Latest reply: Mar 25, 2015 9:31 AM by Puttemans Johan RSS

    Create mapping table based upon defined frequency repartition

    Puttemans Johan

      Hi all,

       

      I've been poundering of this, and I don't get an answer to my questions... So I'd like to have some more people to have a look at it in order to find a solution.

       

      I have a file containing OD's (%origin-%destination) between all stations, together with the following key variables :

       

       

       

       

      [%Line ID],
      Date,
      [%Key to Trains],
      Class,
      %TempProdKey,
      [%Sales Channel],
      Hour,
      [# Revenue],
      [# Travel],
      [%Destination Station],
      [%Origin Station]

      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?