topic Re: Generate random numbers in a field in QlikView

Generate random numbers in a field

rido1421 — Thu, 04 Sep 2014 07:44:38 GMT

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,

Re: Generate random numbers in a field

alexandros17 — Thu, 04 Sep 2014 07:50:51 GMT

use function rand() and multiply the result by 10:

load

...

rand()*10 as myRandom

resident

....

Re: Generate random numbers in a field

rido1421 — Thu, 04 Sep 2014 07:55:03 GMT

how would i limit it between 1 and 10 though?

Re: Generate random numbers in a field

alexandros17 — Thu, 04 Sep 2014 07:59:30 GMT

rand returns numbers from 0 to 1 so left(rand()*10,1) gives you the result

Re: Generate random numbers in a field

MK_QSL — Thu, 04 Sep 2014 08:12:29 GMT

RAND()*9+1 as NUM2

Re: Generate random numbers in a field

MarcoWedel — Thu, 04 Sep 2014 08:17:27 GMT

Transactions:

LOAD

[Sales Value],

[Sales Quantity],

[Agent Name],

Ceil(rand()*11) as [Client Code]

FROM yoursource;

Re: Generate random numbers in a field

simondachstr — Thu, 04 Sep 2014 08:19:13 GMT

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

Re: Generate random numbers in a field

rido1421 — Thu, 04 Sep 2014 08:27:00 GMT

Thank you this is great

Re: Generate random numbers in a field

MarcoWedel — Thu, 04 Sep 2014 11:00:29 GMT

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

Re: Generate random numbers in a field

simondachstr — Thu, 04 Sep 2014 11:15:15 GMT

According to the QV Reference Manual v11 rand() returns a random number between 0 and 1, very possibly excluding 0 and 1.

Re: Generate random numbers in a field

rido1421 — Thu, 04 Sep 2014 11:30:50 GMT

HI Marco

qlikview 11...

Re: Generate random numbers in a field

MarcoWedel — Thu, 04 Sep 2014 11:38:45 GMT

And it delivers 1 to 10 using this expression?

regards

Marco