Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
davidsimpson
Contributor

Generate 6 different random numbers

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?).

9 Replies
Highlighted
adamdavi3s
Honored Contributor

Re: Generate 6 different random numbers

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

Highlighted
MVP
MVP

Re: Generate 6 different random numbers

try this

First 6

load Distinct

  ceil(rand()*20) as random

AutoGenerate 1000000

;

Highlighted
MVP & Luminary
MVP & Luminary

Re: Generate 6 different random numbers

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

Highlighted
davidsimpson
Contributor

Re: Generate 6 different random numbers

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...

Highlighted
davidsimpson
Contributor

Re: Generate 6 different random numbers

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

Highlighted
MVP
MVP

Re: Generate 6 different random numbers

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

R:

load Distinct

  ceil(rand()*20) as random

AutoGenerate 1000000

;

R2:  NoConcatenate First 6 load * Resident R;

DROP Table R;

Highlighted
davidsimpson
Contributor

Re: Generate 6 different random numbers

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!

Highlighted
davidsimpson
Contributor

Re: Generate 6 different random numbers

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

Highlighted
davidsimpson
Contributor

Re: Generate 6 different random numbers

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.