Random selection in listbox

[#1]

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

[#2]

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]

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 · Accepted solution]

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

[#5]

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

[#6]

Hi Wizardo

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

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

[#7]

Hi Rob

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

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

[#8]

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

[#9]

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

[#10]

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