topic Re: Rand with specific percentage allocation in QlikView

Rand with specific percentage allocation

zozo1992 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Rand with specific percentage allocation

sunny_talwar — Wed, 27 Jun 2018 15:43:48 GMT

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;

Re: Rand with specific percentage allocation

gvollmaier — Wed, 27 Jun 2018 15:54:53 GMT

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;

Re: Rand with specific percentage allocation

zozo1992 — Fri, 29 Jun 2018 15:08:44 GMT

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.