

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Generate random numbers in a field
Hi There
I would like to create a field with random numbers within a table ranging from 1- 10
I know the Autonum funtion doesnt work like this, im using it to display what i require...
Load
Transactions:
[Sales Value],
[Sales Quantity],
[Agent Name],
Autonum(10) as [Client Code]
Regards,
- Tags:
- qlikview_scripting
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
rand returns numbers from 0 to 1 so left(rand()*10,1) gives you the result


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
use function rand() and multiply the result by 10:
load
...
rand()*10 as myRandom
resident
....


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
how would i limit it between 1 and 10 though?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
rand returns numbers from 0 to 1 so left(rand()*10,1) gives you the result

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
RAND()*9+1 as NUM2


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Transactions:
LOAD
[Sales Value],
[Sales Quantity],
[Agent Name],
Ceil(rand()*11) as [Client Code]
FROM yoursource;

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For a better probability distribution (normal distribution) I recommend using:
=ceil(norminv(rand(), mean, stdev))
In your example e.g.
=ceil(norminv(rand(),5,3))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you this is great


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Which version of QlikView did you test the code on, that you marked as correct?
I only get 0 to 9.
Possibly a bug?
thanks
regards
Marco

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
According to the QV Reference Manual v11 rand() returns a random number between 0 and 1, very possibly excluding 0 and 1.

- « Previous Replies
-
- 1
- 2
- Next Replies »