17 Replies Latest reply: Nov 17, 2011 11:40 AM by Luis Laura RSS

    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

        • Random selection in listbox

          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

              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
              DANIEL CHOTZEN

              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

                  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

                • Random selection in listbox
                  Rob Wunderlich

                  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

                      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

                        • Random selection in listbox

                          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