Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
zozo1992
New Contributor II

Rand with specific percentage allocation

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

1 Solution

Accepted Solutions

Re: Rand with specific percentage allocation

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;

3 Replies

Re: Rand with specific percentage allocation

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
New Contributor

Re: Rand with specific percentage allocation

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
New Contributor II

Re: Rand with specific percentage allocation

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.