topic Random selection in listbox in QlikView

Random selection in listbox

Fri, 16 Apr 2010 23:17:44 GMT

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

Random selection in listbox

Sat, 17 Apr 2010 04:26:45 GMT

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

Random selection in listbox

wizardo — Sat, 17 Apr 2010 09:08:36 GMT

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

Random selection in listbox

rwunderlich — Sat, 17 Apr 2010 19:21:14 GMT

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

Random selection in listbox

Mon, 19 Apr 2010 08:38:20 GMT

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

Random selection in listbox

Mon, 19 Apr 2010 09:11:16 GMT

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

Random selection in listbox

Mon, 19 Apr 2010 09:21:03 GMT

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

Random selection in listbox

Mon, 19 Apr 2010 10:57:49 GMT

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

Random selection in listbox

Mon, 19 Apr 2010 11:55:43 GMT

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

Random selection in listbox

Mon, 19 Apr 2010 12:16:37 GMT

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

Random selection in listbox

Mon, 19 Apr 2010 12:45:42 GMT

Hi,

I am using 9.00.7440.8 SR3

Lars

Random selection in listbox

Mon, 19 Apr 2010 12:57:56 GMT

hi lars,

probably check with your document properties security settings

thanks

Random selection in listbox

Mon, 19 Apr 2010 13:22:31 GMT

hi lars,

i think you have your "current local security" set as " dont run at all " in the edit module for macros.

pls set it to as only safe mode.

i hope that it should work now.pls check the attached application

thanks

Random selection in listbox

Mon, 19 Apr 2010 19:28:14 GMT

Based on the feedback I trust that Rob's solution does the trick!

Unfortunately it just doesn't work on my installation. I even tried another installation on QlikView with the same negative result. My security is set as proposed, but when I hid the button the selection is cleared completely from the listbox.

I can see though that the "back/forward" changes as if selections are in fact made with the macro - but no effect in the list box [:'(]

Thanks anyway

Lars

Random selection in listbox

Sun, 15 Aug 2010 22:08:40 GMT

I did'nt get Rob's function to work first... But then I realized that the number returned from the Rnd() funktion contained a , and QV expected a . inorder to evaluate the function...
So I could be due to system settings...

I did a simple replace and got Rob's script to work...

BR
Hans

Random selection in listbox

Wed, 13 Oct 2010 13:37:43 GMT

Hi there,

Does anyone have an idea how i can use this code and randomly select for example 100 random articles?

BR Daniel

Random selection in listbox

rwunderlich — Mon, 18 Oct 2010 19:52:49 GMT

You could wrap it in a While loop that runs until you get the count you want. Be careful 🙂

-Rob

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

Re: Random selection in listbox

llauses243 — Thu, 17 Nov 2011 16:35:59 GMT

Hi Lars,

This is my offer, no depending on version

Pls to see image attached

Good luck, Luis

Very thx Rob.