Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
zozo1992
Contributor II
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
sunny_talwar

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;

View solution in original post

3 Replies
sunny_talwar

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

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

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.