Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
davidsimpson
Creator
Creator

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
adamdavi3s
Master
Master

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

maxgro
MVP
MVP

try this

First 6

load Distinct

  ceil(rand()*20) as random

AutoGenerate 1000000

;

marcus_sommer
MVP & Luminary
MVP & Luminary

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

davidsimpson
Creator
Creator
Author

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

davidsimpson
Creator
Creator
Author

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

maxgro
MVP
MVP

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;

davidsimpson
Creator
Creator
Author

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!

davidsimpson
Creator
Creator
Author

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

davidsimpson
Creator
Creator
Author

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.