Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Random selection in listbox

Hi Qliks!

I need to make a button that will select a single random value from a listbox. I am thinking that to make it easier the listbox could contain a linear range of numbers from 0 -> n.

I tried to write a macro like

Sub test

x=4

ActiveDocument.Fields("F1").Select x

End Sub

Now - I just need to figure out how to generate the variable x based on the total number of rows from the list box since the row number will grow in the model.

Your help is highly appreciated

Lars Madsen

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

How about:

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

This will work for any type of values in F1. You don't have to restrict the list to numbers.

You may think you could use the Qlikview rand() function in the Evaluate, but my experience is that the numbers aren't very random that way. Seems to get stuck on a two number loop after a while. The VBS Rnd() function works better.

-Rob

View solution in original post

17 Replies
Not applicable
Author

hi,

you can modify your macro a little to be like this

Sub test

mySelect= ">=0" & "<=n"

ActiveDocument.Fields("F1").Select mySelect

End Sub



i hope this works

thanks

wizardo
Creator III
Creator III

Hi Lars,

you could use this macro in the PostReload of the document.

or turn it into a function and call it from your macro

or just use the lines from it inside your macro

this will give you a variable with the max number to pass to the rand() function

sub Get)LB_numofrows

set LB= ActiveDocument.GetSheetObject( "LB01" )
vNumOfRows= ActiveDocument.GetVariable("vNumOfRows").getcontent.string
h = table.GetRowCount

vNumOfRows.setContent h, true

end sub

another way might be to (in the script) count() the number of the distinct values in the field that's gonna be used in the ListBox. an plant it into a variable

hope it helps

Mansyno

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

How about:

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

This will work for any type of values in F1. You don't have to restrict the list to numbers.

You may think you could use the Qlikview rand() function in the Evaluate, but my experience is that the numbers aren't very random that way. Seems to get stuck on a two number loop after a while. The VBS Rnd() function works better.

-Rob

Not applicable
Author

Hi tauqueer

Thanks for you reply. I don't understand your solution. Am I supposed to replace "n" with the actual number of lines in my listbox?

In that case

  • How will it work dynamically?
  • Will this not only select all the line in the list box and not just one unique selection?

Regards,

Lars

Not applicable
Author

Hi Wizardo

Ah - you are right, making the macro in the script is of course the most simple solution Embarrassed

I guess it will restrict me to using numbers in the listbox then - which is okay.

I am not an expert in macros - so I am affraid I can not figure out your macro - at least it doesn't work for me when pasting into the edit module. The script generates and error: "Object required(...)

Thanks for your effort

Lars

Not applicable
Author

Hi Rob

Your solution is as always simple and beautiful - I can almost understand what you have suggested Surprise

Anyway - I need some more help. I just pasted your solution into the Edit Module to see what happens - unfortunately I don't get any value of the expression (I tried to sent it to a MsgBox)

  • The Ceil function doesn't seem to be recognized in the Edit Module ?
  • What will the round() give me compared to Ceil?
  • The Rnd() seems to generate the same string of values - the first value will always be 0.7055475. That is - if I change something in the edit module that rnd() - wil somehow be "reset" and then follow the same list of random values ... is that intented?

I might be way off in intepreting your macro - bear in mind that VBscript is a mystery to me so pls. guide me in your solution.

Thanks

Lars

Not applicable
Author

hi lars,

rob's solution is simply awesome. its working fine for me.

the ceil function is recognizable in edit module and as far as round() is concerned,

in the expression

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

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

first Rnd() generates a random number between 0 and 1 which when multiplied by total no of fieldvalues( FieldValueCount('F1'))

in field F1 gives a value less than or equal to total no of fieldvalues in F1 which on ceiling gives a unique integer value less than or equal to total no of fieldvalues in F1.

if you use round()instead of ceil, let us suppose you have 10 values in F1.

and Rnd() generates a random number say 0.67 , so 0.67*10 gives 6.7 which on rounding will give 7 so u will get the 7th value in F1

but suppose Rnd() generates a random number say 0.63 , so 0.63*10 gives 6.3 which on rounding will give 6 so u will get the 6th value in F1.

but if u use ceil function , in both the above case ceil() will give u 7 only ie 7th value in F1.

there will be no ambiguity.

thanks

Not applicable
Author

Thanks for that fine explenation tauqueer!

Anyway - I am doing something completely wrong. In the enclosed example I entered Rob's Macro into the Edit Module - when I press my button nothing happens.

What am I doing wrong here?

Lars

Not applicable
Author

hi lars,

i saw your application and everything is working fine.

i am surprised to hear that its not working on your system.

can you tell me which version of qlikview you are using

thanks