1 Reply Latest reply: Nov 4, 2015 11:23 AM by Yang Zhao RSS

    Errors in Sample X records from the chart script

      Hi Experts,


      I am new to Qlik VB script. I created following script based on my understanding on my knowledge with VBA. I then saved this script in Edit Module and linked the action to a button so every time I click the button it could randomly select x samples from the chart.


      Following is my script. All I am trying to do is to randomly generate a row number and put that row number in an array so I can check if that sample is already selected.


      The check function in Qlikview did not note any syntax errors. However when I actually click the button, it always open the Edit Module without doing any sampling. Would someone be able to point out anything incorrect in my script and how to fix. I think the vPopulationSize = FieldValueCount("RecordNumber") was not correct but wasn't sure why. Please help!


      Thank you!



      SUB SelectRandom



        'Clear any exsiting selection on Record Number


        'Pulls in the sample size from the slider

        vSampleSize = CInt(ActiveDocument.Variables("vAuditSelect").GetContent.String)


        vPopulationSize = ActiveDocument.Evaluate("FieldValueCount('RecordNumber')")


           IF vPopulationSize < vSampleSize Then msgbox ("Sample size can not be more than population size")


        vSelectedCount = 0

        vSampleRow = 0



        ' define an array to store the list of selected record number

        dim vArray_RcrdNbr(60)




        v_RcrdNbr = 0

        v_break = 0



      While vSelectedCount < vSampleSize



      vSampleRow = ActiveDocument.Evaluate("ceil(" & Rnd() & " * " & vPopulationSize&")")


        'check if the random sample has been selected in the previous selection, if yes, set the v_break flag to 1

        For each v_RcrdNbr in vArray_RcrdNbr

        If vSampleRow = v_RcrdNbr Then v_break = 1



        IF v_break = 0 Then



          'Selects a record number in the range of records present in the dataset

        val = ActiveDocument.Evaluate("FieldValue('RecordNumber', vSampleRow)")


              vSelectedCount = vSelectedCount +1


          'add the sampled row number to the array

           vArray_RcrdNbr(vSelectedCount) = vSampleRow


          'Selecting the record

          ActiveDocument.Fields("RecordNumber").ToggleSelect val



        End if





      End Sub