Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

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

1 Reply
Not applicable

Re: Errors in Sample X records from the chart script

Figured it out by myself. The reason it is not working is that Qlikview does not take expression if it is string. Need to use evaluate() to pass the value of the expression to the variable.

Community Browser