Rand with specific percentage allocation

[zozo1992]

Hi all,

I am trying to allocate random user numbers to certain values but with specific percentage allocation. This is my script:

LOAD

Ceil(RAND()*100)                                                                  AS User_Number,

pick(ceil(rand()*6),'Non User',Trialist','Advocate Key')           AS Adoption_User

AutoGenerate 1000;

However, I want to allocate these three adoption user groups based on percentages, so Non User would have 10% of the autogenerated rows, Trialist 20% of the autogenerated rows, and Advocate Key 70%.

I have tried the following scripts with no success but they could help spark the solution:

if((rand()<=0.10+now()*0) = 0, 'Non User', 'Advocate Key') (Maybe this with nested IF?)

if(User_Number>0,Pick(Match(-1,User_Number<=0.10,User_Number<=0.20,User_Number<=0.70),'Non User','Trialist','Advocate Key')) AS Adoption_ladder

Thank you in advance

[sunny_talwar · Accepted solution]

Why not try like this

Table:

LOAD

Ceil(RAND()*100)                                                                  AS User_Number,

'Non User'           AS Adoption_User

AutoGenerate 100;

Concatenate(Table)

LOAD

Ceil(RAND()*100)                                                                  AS User_Number,

'Trialist'           AS Adoption_User

AutoGenerate 200;

Concatenate(Table)

LOAD

Ceil(RAND()*100)                                                                  AS User_Number,

'Advocate Key'           AS Adoption_User

AutoGenerate 700;

[gvollmaier]

Or:

LOAD

Ceil(RAND()*100) AS User_Number,

if(tmprand<0.1, 'Non User',

if(tmprand<0.3, 'Trialist','Advocate')) as Adoption_User

;

LOAD rand() as tmprand

AutoGenerate 1000;

[zozo1992]

Unfortunately, this if statement will ignore values if rand() is less than <0, etc. Thus it will not be correct as I want all the values to be allocated and non ignored.