Step Through Field Values

Author's Note: Although this technique is still sometimes very useful, there is an easier way to accomplish this in QlikView without a macro (Click here to read it).

Often, when we're analyzing data we need to examine the tables and graphs on our QlikView report for each value of a field. For example, we might need to check on inventory values and forecast error separately for each warehouse location or maybe for each product category or maybe we need to look at the data separately for each month of the year. Here's something that helps with that process. Add a button to your report and name the button "Step Through Field Values". Then, add this macro to the module and associate it with the button:
SUB StepThroughValues
'--------------------
'Step through all the available values for a field
'selecting each value one at a time
fieldName=trim(inputbox("Enter a field to cycle through. (Case sensitive and spelling counts!)","Step Through Values","Material"))
If fieldName="" then
Exit sub
End if
Set val=ActiveDocument.Fields(fieldName).GetPossibleValues(20000)
'Set val=activedocument.Fields(fieldName).GetSelectedValues(20000)
For i=0 to val.Count-1
ActiveDocument.Fields(fieldName).Select val.Item(i).Text
returnval=msgbox(val.Item(i).Text&chr(13) & i+1 & " of " &val.Count & chr(13 )& chr(13)& "Click:" & chr(13) & " YES: to create Bookmark" & chr(13) & " NO: to move to next entry" & chr(13) & " CANCEL: to quit", 259, "Step Through Values")
if returnval=2 then
Exit For
ElseIf returnval=6 then
bmarkName=inputbox("Enter a Bookmark Name", "User Entry", "BookMark " & i+1 & ", " & fieldName & "=" & val.Item(i).Text)
ActiveDocument.CreateDocBookmark false, bmarkName
End if
Next
Activedocument.Fields(fieldName).Clear
Set val=Nothing
End sub

I know that this blog format and your browser will be wrapping some of the lines in the macro and removing the indentation that I use to indicate code structure so be careful. You should be able to select and copy the code from your browser window and then paste it into the QlikView macro.

When you click the button on your report a window will appear that asks you for the name of the field to step through. Use the actual field name that was loaded into QlikView. This is case sensitive and must be the correct field name spelling. The macro will then select the first available value for the field and offer you the opportunity to create a bookmark for it. Click on No to make the macro move on and select the next possible value for the field. The bookmark is just to help you come back later and do further analysis. The macro could also be coded to write out a chart as a file or save a graph or do some other useful thing.

Note that there's a commented out line in the macro that will make it step through each of the currently selected values for a field. If you prefer that functionality, just uncomment that line and comment out the previous line to make the button step through the selected values instead of all possible values. In either case, by the time you are finished with the button it will have worked with selections for the field so you may have to restore your original selections (or use the Back arrow to move back through selections).

I hope you find the button useful. Please add a comment to this posting if you have any improvements or suggestions.