Qlik Community

QlikView Documents

Documents for QlikView related information.

How to generate random numbers with seed in QlikView

dgreenberg
Contributor III

How to generate random numbers with seed in QlikView

   

How to generate random numbers with seed in QlikView

 

I was recently asked to produce a sampling routine in QlikView using random with seed.

 

QlikView like Excel and many other programs doesn’t have a native random with seed function, its random function is truly random.

 

The users need to be able to reproduce the same results at any time in the future for auditability and that is why they need to use random with seed.

The logic here is that if we use the same starting seed and poll the same set of records (let’s say April 2016) the partners randomly selected will always be the same so we could reproduce the results until the next month when there is a different pool of records and a different starting seed.

The requirements get a little fancier as we are sampling 25 partners and the random number generated the first time becomes the seed for the 2nd random selection which becomes the seed for the 3rd random selection and so on until all 25 selections are made and then the 25th row selected becomes the seed when this is run again next month.

   

Random seed

From Wikipedia, the free encyclopedia

random seed (or seed state, or just seed) is a number (or vector) used to initialize a pseudorandom number generator.

For a seed to be used in a pseudorandom number generator, it does not need to be random. Because of the nature of number generating algorithms, so long as the original seed is ignored, the rest of the values that the algorithm generates will follow probability distribution in a pseudorandom manner.

 

Random seeds are often generated from the state of the computer system (such as the time).

How do we do this in QlikView when it doesn’t have a native random with seed function?  The Rand() function doesn’t allow you to provide a starting seed.

The answer is to write a VBScript or Jscript function that is called in your load script.

 

function myRandom(x,y)
Rnd(-1)
Randomize(x)
strMsg = strMsg & (int(y*rnd()) +1)
myRandom=strMsg
End function

 

The above function has to parms X is the seed and Y is the max value (pick a number between 1 and 100 for example).

calling myRandom(.551,100) would generate a random number between 1 and 100 with a seed of .551

 

This is called in the load script with a let statement

 

let vRand=myRandom(.551,100); //This picks a random number between 1 and 100 with a seed of .551

You can also use dollar sign expansion to pass variables like below

 

let vRand=myRandom($(vRand2),$(vNumberOfRows2));

 

 

Comments
MVP
MVP

Thanks Dan.

I believe rnd() VBScript function is returning a value from the interval [0,1[,  hence  I would expect

let vRand=myRandom(.551,100);

//This picks a random number between 0 and 99 (incl.) with a seed of .551

We had a very recent discussion in the forum if the QV function RAND() also returns a value from interval [0,1[

Do you know if this is the case?

dgreenberg
Contributor III

You appear to be correct.  I took the function and put it in Excel with a slight modification

Function myRandom(x)

   Rnd (-1)

   Randomize (x)

    strMsg = strMsg & Int(10 * Rnd())

    myRandom = strMsg

End Function

Here I use 10 in stead of passing a variable for the between 1 and n or as I stand corrected between 0 and n-1.

Good catch I am sure it works the same way in Excel so I will need to add 1 to my random row returned.

MVP
MVP

Why not building a script SUB for this? So we can use it in Sense too..
Should I go for it?

Emmanuelle-Bustos
Valued Contributor

Interesting!

Version history
Revision #:
1 of 1
Last update:
‎05-20-2016 09:30 AM
Updated by: