Random field lookup but weighted?

[osninc]

Hi,

I have a FM7 DB that is simulating inbound mobile phone call transactions coming from various wireless carriers. I need a script or calculation field that can automatically populate a field at the time of record creation named CARRIER_NAME. The catch is I need to do it randomly yet over time I need to adjust it for relative market share percentage of each wireless carrier.

I have 2 tables:CARRIERS & TRANSACTIONS. The CARRIERS table has just 7 records listing the names of the top 7 US wireless carriers in the NAME field and market share in the SHARE field. The TRANSACTIONS table has a TIMESTAMP field indicating the time of the simulated call and the CARRIER_NAME field which is what I need to be populated randomly with data from the NAME field from one of the 7 records in the CARRIERS table yet weighted based on the SHARE field.

Does anyone have any ideas on how to do this in FM7?

Thanks in advance for your help.

Tim J.

[RalphL]

Assume the carrier are A, B, C ...F & G. Assume A gets 30%, B gets 23%, C gets 17%, D gets 10%, E gets 8%, F gets 7% & G gets 5%. Total is 100%. Use the randon function to generate a random number R. Multiply R by 100000 and take the mod of 100. This will give you a number from 0 to 99. (You may have to round this or use the integer.) Add 1 to this so now you have a number between 1 and 100. Use the case function as follows:

Case (R <= 30; A;

R <= 53; B;

R <=70; C;

R <= 80; D;

R <= 88; E;

R <= 95; F;

G)

for the values I assuemd.

[osninc]

Thanks Ralph! that works perfectly