topic Re: Randomizing Macro in QlikView

Randomizing Macro

christian77 — Fri, 08 Feb 2013 10:51:06 GMT

Hi:

I have a list of 10 Client_id. (1,2,3,4,5,6,7,8,9,10)

I need to select ramdomly a sample of 3. (3,4,9)

They have to be different.

I have the process in progress but I still need to compare each item with the list. If it is in the list, repeat the process, otherwise go to next.

So my problem is, does the list contain the item?

What is the function for that?

Thanks.

Re: Randomizing Macro

Fri, 08 Feb 2013 11:41:27 GMT

try to create string from Your clients list string = 1,2,3,4,5,6,7,8,9,10

and each tome You generate a sample check it by index() function

index(string,sample)

Re: Randomizing Macro

christian77 — Fri, 08 Feb 2013 11:45:19 GMT

The thing is that QlikView Visual Basic is so basic that it doesn't have that function, neither the function Contains.

It is a very particular and narrow VisualBasic.

Re: Randomizing Macro

marcus_sommer — Fri, 08 Feb 2013 16:46:30 GMT

You can create the sample in GUI in a variable per rand() and this result to request in macro.

- Marcus

Re: Randomizing Macro

sbaldwin — Fri, 08 Feb 2013 16:56:02 GMT

Hi, couldn't you just write something like this:

sub rr
a = int(10*rnd())+1

b=-1
c=-1
do while (a=b or b=-1)
b = int(10*rnd())+1
loop

do while (a=b or a=c or b=c or c=-1)
c = int(10*rnd())+1
loop

a = "("&a&"|"&b&"|"&c&")"
x= ActiveDocument.GetVariable("v1").setcontent(a,false)

end sub

then do a select in field action using ='$(v1)'

Thanks

Steve

Re: Randomizing Macro

christian77 — Mon, 11 Feb 2013 08:42:24 GMT

Thank you all for your answers. I coud use this one that I found. But it does not work in QV. Remember that I don't want repeated numbers.

Private Shared Sub GetRandom(ByVal oMax As Integer, ByRef currentVals As List(Of Integer))
    Dim oRand As New Random(Now.Millisecond)
    Dim oTemp As Integer = -1
    Do Until currentVals.Count = IMG_COUNT
        oTemp = oRand.Next(1, oMax)
        If Not currentVals.Contains(oTemp) Then currentVals.Add(oTemp)
    Loop
End Sub

On the other hand they are thousands of clients. This module works fine if the difference between the ordinal of the list and the ordinal of the sample is big.

I went for the next solution:

Every time I reload my document I obtain a randomized list of clients. After that, in the macro, I just have to pick up the first ones, it doesn't matter what's the cardinal or the sample size.

That's it.

Thanks everybody anyway.

Re: Randomizing Macro

flipside — Mon, 11 Feb 2013 08:55:51 GMT

Hi Christian,

Put your values into an array and when you randomly select a value, replace it with an empty string (or any marker you wish) so it can be checked on the next sample retrieval. Something like this might work for you (note the Randomize command at the top - very important).

sub RandomSampling

Randomize

'Dummy dataset in an array

a=Array(1,2,3,4,5,6,7,8,9,10)

'Set values chosen to empty string

v = ""

maxLoop = 3

'Define loop count, checking not greater than number of values in array

if maxLoop > Ubound(a) then

maxLoop = Ubound(a)

end if

For z = 1 to maxLoop

'Seed the do while flag

i = 0

Do While i=0

x = Int((Rnd()) * Ubound(a)+0.5)

'msgbox x

if CStr(a(x)) <> "" then

v = CStr(a(x)) & ", " & v

a(x) = ""

i=1

end if

loop

Next

v = left(v,len(v)-2)

Msgbox ("I have chosen values: " & v)

end sub

flipside

Re: Randomizing Macro

christian77 — Mon, 11 Feb 2013 09:07:54 GMT

Thank you flipside.

Your macro is good, but I have already solved my problem.

Also, in my first post, I gave an example of a list of 10 elements. The real number can go from 1 to 100 thousand.

On the other hand, if you have to choose 90 values of a list of 100, when you are in the value 85, the loop is going to rebound inside itself many times.

The best and simple solution is bringing the list already randomized by using a SQL built funcktion for this kind of aleatory sampling. QV does not have this kind of random sampling.

SO THE SOLUTION IS:

In the script:

ClientRandomizedList:

LOAD

Client_id as Client_id,

Client_id as Client2_id;

sql select Client_id

order by newid();

and this is the correct answer.

Re: Randomizing Macro

flipside — Mon, 11 Feb 2013 09:21:47 GMT

If you are choosing more than 50% of values, then you can do it the other way round and select values to discard so as to mitigate the potential of long-running Do While Loops, but if you have access to SQL then this is possibly a better way as you say.

flipside