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.
From Wikipedia, the free encyclopedia
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.
strMsg = strMsg & (int(y*rnd()) +1)
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