Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
puttemans
Valued Contributor

Create mapping table based upon defined frequency repartition

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?

Community Browser