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

        ActiveDocument.Fields("RecordNumber").Clear

        '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

        Next

        msgbox(vSampleRow)

        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

       

       

       

      Wend

      End Sub