topic Re: Generate 6 different random numbers in QlikView

Generate 6 different random numbers

Anonymous — Mon, 14 Nov 2016 11:23:21 GMT

Hi all, I'm struggling to find a smart way to generate 6 different random numbers in a range (1 - 20 for example). I have used a nested if to check they are different and then set a variable but it's not automatic so I need to regenerate if I find duplicates.

=if(aVar=bVar or aVar=cVar or aVar=dVar or aVar=eVar or aVar=fVar,0,
if(bVar=cVar or bVar=dVar or bVar=eVar or bVar=fVar,0,
if(cVar=dVar or cVar=eVar or cVar=fVar,0,
if(dVar=eVar or dVar=fVar,0,
if(eVar=fVar,0,
1)))))

I'm using a macro to generate the numbers in fields and then getfieldselection to set variables. Probably not the smartest way.

This script was taken from the forum (sorry to the author I can't remember where I got it - but thanks). The fields have been set by Load * inline.

Sub SelectRandomA
val = ActiveDocument.Evaluate("FieldValue('A', ceil(" & Rnd() _
& " * FieldValueCount('A')))")
ActiveDocument.Fields("A").Select val
End Sub

What's the smart way to come up with different random numbers? I kind of need a loop on my button that checks and starts again if not all different (maybe?).

Re: Generate 6 different random numbers

adamdavi3s — Mon, 14 Nov 2016 12:28:57 GMT

Something like this might be better, while not infallible you would be VERY unlucky to generate 6 numbers the same from 60!

However you may need to up that if you require and integer and not a number

test:

first 6 load distinct value;

load 20*rand() as value

autogenerate(60);

let vA = FieldValue('value',1);

let vB = FieldValue('value',2);

let vC = FieldValue('value',3);

let vD = FieldValue('value',4);

let vE = FieldValue('value',5);

let vF = FieldValue('value',6);

Re: Generate 6 different random numbers

maxgro — Mon, 14 Nov 2016 13:11:32 GMT

try this

First 6

load Distinct

ceil(rand()*20) as random

AutoGenerate 1000000

;

Re: Generate 6 different random numbers

marcus_sommer — Mon, 14 Nov 2016 13:18:54 GMT

I think you meant this posting: Random selection in listbox and here is a version with a loop: Random selection in listbox‌.

Beside them you could try to use an action with a search-expression like:

= '(' &
chr(34) & FieldValue('YourField', ceil(rand() * FieldValueCount('YourField'))) & chr(34) & '|' &
chr(34) & FieldValue('YourField', ceil(rand() * FieldValueCount('YourField'))) & chr(34) & '|' &
chr(34) & FieldValue('YourField', ceil(rand() * FieldValueCount('YourField'))) & chr(34) & '|' &
chr(34) & FieldValue('YourField', ceil(rand() * FieldValueCount('YourField'))) & chr(34) & '|' &
chr(34) & FieldValue('YourField', ceil(rand() * FieldValueCount('YourField'))) & chr(34) & '|' &
chr(34) & FieldValue('YourField', ceil(rand() * FieldValueCount('YourField'))) & chr(34) &
')'

- Marcus

Re: Generate 6 different random numbers

Anonymous — Mon, 14 Nov 2016 14:04:06 GMT

Some reloads give only 5 answers. I'm guessing because there was a duplicate and the Distinct got rid of it. Nice tidy bit of code though...

Re: Generate 6 different random numbers

Anonymous — Mon, 14 Nov 2016 14:07:33 GMT

Ah, yes I do need an integer otherwise then yes this would work. Plus I need all to be different to each other and not just not all 6 the same (which would be an amazing coincidence).

Re: Generate 6 different random numbers

maxgro — Mon, 14 Nov 2016 14:15:18 GMT

maybe the first 6 should be in a resident load instead of a preceeding load

load Distinct

ceil(rand()*20) as random

AutoGenerate 1000000

;

R2: NoConcatenate First 6 load * Resident R;

DROP Table R;

Re: Generate 6 different random numbers

Anonymous — Mon, 14 Nov 2016 14:32:18 GMT

Marcus, thanks, the link to the Random Selection with loop is just what I wanted.

Sub SelectRandom

ActiveDocument.Fields("F1").Clear

While ActiveDocument.Evaluate("getSelectedCount(F1)") < 100

val = ActiveDocument.Evaluate("FieldValue('F1', ceil(" & Rnd() _

& " * FieldValueCount('F1')))")

ActiveDocument.Fields("F1").ToggleSelect val

Wend

End Sub

This does the job perfectly.

The expression you added also gives me a nice list. Splendid!

Re: Generate 6 different random numbers

Anonymous — Mon, 14 Nov 2016 14:37:20 GMT

Yes, that also does the trick. More than one way to skin this cat. Many thanks.

Re: Generate 6 different random numbers

Anonymous — Mon, 14 Nov 2016 14:48:49 GMT

Thanks, gives different numbers of values from 4 to 7 (so far). Not sure why but the other solutions posted here are working ok. Many thanks.