Create mapping table based upon defined frequency repartition

puttemans — Wed, 25 Mar 2015 13:31:55 GMT

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?

topic Create mapping table based upon defined frequency repartition in QlikView

Create mapping table based upon defined frequency repartition