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