Rename a Cycle Group

I often use Rob Wunderlich’s excellent Copy Groups utility to copy cycle groups from one QlikView document to another; or to copy a cycle group from a document back into the same document to make a duplicate (to see the utility and get your own copy, search on ”QlikView Cookbook Copy Groups Utility”). But, sometimes after copying the cycle group, the name of the group may not be ideal.

Author's note:  I removed the rest of this post after seeing Andre's comment. Thanks, Andre - that was something I didn't know... and it's much simpler than what I wrote.

Portion of Andre's comment:

A group can be renamed by going to Document Properties | Groups | Select Group | Edit | Enter New Name | OK

This changes where the group is used in a chart dimension as well

 

Data Profile Tool

I’ve written in this blog before about how I’m often called on to validate a new data file from a client or to examine a new data extract. My first step is to load data into a copy of Steve Dark’s Data Profiler (Steve's original post is useful - search on quickintelligence data profiler). The data profiler gives me information about exactly what values exist in each of the fields.

I’ve made a few modifications to Steve’s original document:

• Added a table box that adjusts itself to the fieldnames in the file
• Added a small macro that updates the Statistics Box with the fieldname automatically as different fieldnames are selected. A trigger detects the change and automatically configures the Statistics Box
• Added a tab to help search for duplicate values in either a field or combination of fields
• A check in the loadscript to see if the file exists and if it does not exist then it opens a msgbox from the loadscript using a function

You can download an example copy of the file data profiler that I use by clicking HERE

.

I also keep a version of the data profiler that is tailored for loading data from an Oracle table and a version of the profiler tailored for loading data from a qvd file. I think I use the data profiler tools almost every day to examine data in files and tables to help with QlikView document design and other reporting requirements.

These two examples have no data loaded - you will have to do a reload yourself from your own data:

• You can download an example copy of the profiler I use for qvd files by clicking HERE
• You can download an example copy of the profiler I use for Oracle tables by clicking HERE

  ★★★

General File Loading Technique

Here’s a technique I first used several years ago when I started a project where we had several dozen data files, all of them containing different data, in a delimited format and each of them with a header record as the first record containing the field names. The loadscript code below shows how we could load each of the files into different QlikView tables using the same loadscript.

The code below has the filename in the code but it could be loaded into the QlikView document by including the filename as a variable in the command line execution of QlikView (for an example, search on "qlikview maven command line and automation").  Other options include building a loop and loading each of files in a folder (search on “qlikview maven loading all of the files from a folder”); or, if you are comfortable with macro module code, you could use a browse-for-file technique to make it easy to find and load a filename (search on “qlikview maven browse-for-file macro button”).

Here’s the loadscript example code:  (some of the long lines are wrapped around as you view the code in the blog window but they will go back to normal if you copy and paste the code into a text file or loadscript edit window) 

Set xfile='customer_data_12.csv'; //file pathname
Let xfile=Upper('$(xfile)'); //optional convert to upper case

FIELDDAY:  //load first record only 
Load * from $(xfile)
(txt, no labels, delimiter is '|') 
where recno()=1;  //note "no labels" 

Set loadstatement='Load '; //now, build the Load statement
For i=1 to 100
Let fn=trim(SubField(peek('@$(i)',0,'FIELDDAY'),';',1));
if '$(fn)'='' then 
  exit for
  end if;
if $(i)=1 then
  Let loadstatement='$(loadstatement) @$(i) as [$(fn)]';  
else
  Let loadstatement='$(loadstatement), @$(i) as [$(fn)]';
  end if
Next

$(xfile): //QlikView table name
$(loadstatement) 
FROM
$(xfile) 
(txt, no labels, header is 1 line, delimiter is '|'); //change delimiter character as needed

Drop table FIELDDAY;

Store $(xfile) into $(xfile).qvd (qvd); //optional save as qvd
//Drop table $(xfile);  //optional drop table

Change the file attributes as needed for your situation: for example, Excel files, skip over unneeded header records, different delimiter, etc.

There are two important ideas illustrated in the example:  first, the idea of loading the header record of a file and building a custom load statement on the spot; second, the idea that you can construct any loadscript statement or part of a statement in a variable and execute it just by putting the variable in the right place in the loadscript.

This technique helped me in a recent project where the data files were supplied with field names in the header record but all of the field names needed a little modification in order to use them. Some of them I converted to upper case and some of them needed to have special characters removed. It was easy to add the necessary text functions to edit the field names as the code assembled the load statement.

It will help you debug and validate this technique if you set the document properties option to write out a loadscript log file when the loadscript runs. The log file will show all of the lines in the loadscript as they were executed with variables fully expanded.

  ★★★

Loading an Unstructured Text File

This is the start of a series of postings about dealing with unstructured text data. I've used QlikView to help analyze message log files and error files and even program source code – there are some things QlikView can do that Notepad style text editors cannot.
First, we need to load the data. It isn’t always a completely unstructured file like my example, often the text is one or more fields inside a structured file or database.  There are various ways to load a text file; this is what I usually put into the loadscript:
TEXTFILE:
LOAD 
RecNo() as recno,
@1 as rectext
FROM
[$(File_Pathname)]
(txt, no labels, delimiter is \x7, no quotes); 

That code will load each record from the text file into the rectext field. I use a variable for the file pathname because I usually use a loadscript like this with a document where the user can supply a filename through an inputbox. Alternatively, you could add browse-for-file functionality like I wrote about in March 2009 (search on QlikView Maven Browse-for-File Macro Button). Because the data is unstructured and the document objects are meant to be used with unstructured data, the document works well as a general-purpose tool that can be used with many types of data in varying situations. 

I defined the file as though it is delimited and made the specified delimiter an uncommon character I don’t expect to find in the data. 

The record number, loaded as recno, from the input file is a useful piece of data and we’ll be using it in some of the example objects over the next few postings.

★★★

Update Document Variables Directly From Excel Spreadsheet

A few years ago I published a blog entry that showed how to copy all of the variables and their values from a QlikView document into an Excel spreadsheet (search on QlikView Maven Listing All Variables and Contents). It was very useful for me, I use it all the time especially when I want to search or sort the variables. Here is the companion to that piece - it is an Excel macro that will copy the data from your spreadsheet into the document variables of a QlikView document. I will say that again - it is an Excel VBA macro (not a QlikView macro).

Copy and paste to install the macro into your Excel spreadsheet and build a button to execute it. You will find it convenient to insert it into the same Excel file that you use for the variable listing I mentioned above. The macro will use a browse-for-file feature so you can select the QlikView document file you want to update. Then, the macro will go down the rows of your active worksheet taking variable names from column A and variable contents from column B and updating the document until it hits an empty cell in column A. It will then tell you how many variables it has processed and ask if you want to save the document. It starts from row 4 of your  worksheet to make it compatible with the earlier spreadsheet macro but you can change the row that it starts with - look for the comment in the middle of the macro.

As always, make a backup copy of your files before running a macro like this. Here's the macro code:


Sub Update_QV_Variables()
'------------------------
'This Excel macro will ask you to select a QlikView document file and
'then it will insert/updateput the variable names and contents from
'your current worksheet into the QlikView document.
'Variable name must be in column A and variable contents in column B.
'The process goes down the rows until it hits an empty cell in column A.
'--QlikView Maven, April 2013
Dim oQV, oDoc, oVars, oTempVar, oThisVar, qv_fn, i, rowlmt, start_row, varctr, x

'Get the QlikView document pathname
qv_fn = Application.GetOpenFilename("QlikView Report File (.),*.*", , "Select a QlikView .qvw Report File", False)
If qv_fn = 0 Then
  Exit Sub
  End If

'Open the QlikView document file
On Error Resume Next
Set oQV = CreateObject("QlikTech.QlikView")
If Err.Number > 0 Then
  x = MsgBox("Sorry, I can't find QlikView", vbOKOnly, "Update_QV_Variables")
  Exit Sub
  End If
Set oDoc = oQV.OpenDoc(qv_fn)
If Err.Number > 0 Then
  oQV.Quit
  x = MsgBox("Sorry, I can't find the QlikView document", vbOKOnly, "Update_QV_Variables")
  oQV.Quit
  Exit Sub
  End If
Set oVars = oDoc.GetVariableDescriptions
If Err.Number > 0 Then
  oQV.Quit
  x = MsgBox("Sorry, I can't find the QlikView document", vbOKOnly, "Update_QV_Variables")
  Exit Sub
  End If
On Error GoTo 0

start_row = 4 'this is the first spreadsheet row with variable data - change as needed
rowlmt = ActiveSheet.UsedRange.Rows.Count
varctr = 0

'Loop through the rows
For i = start_row To rowlmt
  varname = ActiveSheet.Cells(i, 1).Value
  If Len(Trim(varname)) = 0 Then
    Exit For
    End If
  varctr = varctr + 1
  varcontent = ActiveSheet.Cells(i, 2).Value
  Set oThisVar = oDoc.Variables(varname)
    If oThisVar Is Nothing Then
    'must need to create variable
    oDoc.CreateVariable varname
    Set oThisVar = oDoc.Variables(varname)
    End If
  oThisVar.SetContent varcontent, True
  Next

'Finish up
x = MsgBox(varctr & " variables inserted into " & qv_fn & ". OK to Save?", vbOKCancel, "Update_QV_Variables")
If x <> 2 Then
    oDoc.Save
    End If
oDoc.CloseDoc
oQV.Quit
End Sub

★★★

Macro to Copy Cycle Group From One Document to Another

Keeping with the toolkit theme, here's a macro that can copy a cycle group from one QlikView document to another. If you've done much development, you know that it is easy to copy charts and other sheet objects from document to document; but cycle groups cannot be copied and some of them represent a lot of work to completely replicate in another document.

Copy this macro code into the module area of your toolkit document and create a button to execute it. When the macro is executed it will ask for the pathname to the source document containing the cycle group to be copied. Then it will ask for the cycle group name (remember that upper and lower case is important), and then the pathname to the document that the cycle group will be copied into. If either document requires a username or password QlikView will ask for those as the macro opens the documents. As always, make copies of your documents before testing a macro like this.

Here's the macro code you can copy and paste:


Sub Copy_Cycle_Group_Doc_to_Doc
'------------------------------
' /* Copy Cycle Group From One QV Document to Another */
' /* QlikView Maven, March 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar 
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
fn_source=inputbox("Enter pathname to source document containing the Cycle Group you want to copy:","Enter Source Document Pathname")
if trim(fn_source)="" then 'no entry or cancel
  exit sub
  end if

CGName=inputbox("Enter name of Cycle Group to copy:","Enter Cycle Group Name")
if trim(CGName)="" then 'no entry or cancel
  exit sub
  end if
fn_dest=inputbox("Enter pathname to destination document where the Cycle Group is to be inserted:","Enter Destination Document Pathname")
if trim(fn_dest)="" then 'no entry or cancel
  exit sub
  end if

Set objSource=Application.OpenDoc(fn_source)
Set objDest=Application.OpenDoc(fn_dest) 
Set SrcGrp = objSource.GetGroup(CGName)
Set SrcProps = SrcGrp.GetProperties
Set NewGrp = objDest.GetGroup(CGName)
If NewGrp Is Nothing Then 'check if cycle group exists in dest document
  Set NewGrp = objDest.CreateGroup(CGName) 'doesn't exist so create it
Else
  Set NewProps = NewGrp.GetProperties 'does exist so remove old definition
  for i = 1 to NewProps.FieldDefs.Count
    NewGrp.RemoveField 0
    Next
  End If
Set NewProps = NewGrp.GetProperties
'copy elements of cycle group from source to dest
NewProps.FieldDefs.CopyFrom SrcProps.FieldDefs
NewProps.IsCyclic = SrcProps.IsCyclic
NewProps.Labels = SrcProps.Labels
NewProps.Present = SrcProps.Present
NewProps.SortCriterias.CopyFrom SrcProps.SortCriterias
NewGrp.SetProperties NewProps

'we're done, show a message and close down
x=msgbox(CGName&" copied from "&fn_source&" to "&fn_dest&"."&chr(13)&"OK to Save?",vbOKCancel,"Copy_Cycle_Group_Doc_to_Doc")
if x=vbOK then
    ObjDest.Save
    End if
objDest.CloseDoc 'comment out this line if you'd like dest document to stay open for examination
objSource.CloseDoc
set objSource=nothing
set objDest=nothing
set SrcGrp=nothing
set SrcProps=nothing
set NewGrp=nothing
set NewProps=nothing 
End Sub  'end of Copy_Cycle_Group_Doc_to_Doc

★★★

More on Copy Variables From One Document to Another

Here's an alternate version of that macro from last time. This macro can be copied into the module area of a QlikView document (I call mine tookit.qvw) and, when executed, it will copy variables from an external document to another external document. I find this one handier for the kind of work I do.

This document will ask you for the pathname to a source QlikView document containing the variables you want to copy. Then it will ask for the pathname to a destination document that will have the copied variables inserted. If either of the documents requires a password you will be asked for the password as the macro opens the documents. There's no error checking but the most common error for this macro is entering an invalid pathname. After the variables are copied, the macro will ask if it is OK to save the destination document. Remember, as always, to make a backup copy of your documents before testing macros like this.

Here's the code that you can copy and paste into your own toolkit document:


Sub Copy_Variables_Doc_to_Doc
'----------------------------
' /* Copy Variables From One QV Document to Another */
' /* QlikView Maven, February 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar 
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
fn_source=inputbox("Enter pathname to source document containing the variables you want to copy:","Enter Source Document Pathname")
if trim(fn_source)="" then 'no entry or cancel
  exit sub
  end if
Set objSource=Application.OpenDoc(fn_source) 

fn_dest=inputbox("Enter pathname to destination document where variables are to be inserted:","Enter Destination Document Pathname")
if trim(fn_dest)="" then 'no entry or cancel
  exit sub
  end if
Set objDest=Application.OpenDoc(fn_dest) 

'Loop through the variables
set objVars=objSource.GetVariableDescriptions
varctr=0
for i = 0 to objVars.Count - 1
  varctr=varctr+1
  set objTempVar = objVars.Item(i)
  varname=Trim(objTempVar.Name) 
  Set objSourceVar=objSource.Variables(varname) 
  varcontent=objSourceVar.GetRawContent
  'display the variable to check on progress if needed
  'msgbox(varname & " = " & varcontent)

  'update the value of variable in destination document
  Set objDestVar=objDest.Variables(varname)
  If objDestVar is nothing then
    'must need to create variable
    objDest.CreateVariable varname
    Set objDestVar=objDest.Variables(varname)    
    End If

  objDestVar.SetContent varcontent,true

next 'end of loop 

'we're done, show a message and close down
x=msgbox(varctr&" variables copied from "&fn_source&" to "&fn_dest&"."&chr(13)&"OK to Save?",vbOKCancel,"Copy_Variables_Doc_to_Doc")
if x=vbOK then
    ObjDest.Save
    End if
objDest.CloseDoc 'comment out this line if you'd like dest document to stay open
objSource.CloseDoc
set objSource=nothing
set objDest=nothing
set objVars=nothing
set objTempVar=nothing
set objSourceVar=nothing
set objDestVar=nothing
End Sub  'end of Copy_Variables_Doc_to_Doc


★★★

Macro to Copy Variables In From Another Document

My preference when I need to copy variables from one QlikView document to another is to use a small Windows script to do the copying (I wrote about it in 2008 - search on "Copying Variables From One QlikView Document to Another"). But, I know there are some people who can't use a Windows script because of corporate or I.T. rules. Here's a QlikView macro that you can insert into your module area that will copy in all of the variables from another document. 

This macro has you entering a pathname for the document into an input box. For anyone in the advanced class you can replace that with a browse-for-file feature if you think it is worth the time (I wrote about it - search on "Browse-for-File Macro Button"). In the next blog posting ("More on Copy Variables From Document to Another") I show a macro that can be installed once in a toolkit document and it will copy variables from a specified external document to another external document.

Here's the macro code. Try it out - you can copy and paste the text you see in this blog. Remember, as always, to make a backup copy of your document before testing macros like this. To run the macro, create a button for it or for one-time use just click the Test button in the module editor. There's no error checking but the most common error for this macro is entering an invalid pathname. If your source document requires a password then you will be asked for the password when the macro opens the source document.

Sub Copy_Variables
' /* Copy Variables In From Another QV Document */
' /* QlikView Maven, February 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar 
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
fn=inputbox("Enter pathname to source document containing variables you want to copy:","Enter Pathname for Source Document")
if trim(fn)="" then 'no entry or cancel
  exit sub
  end if
Set objSource=Application.OpenDoc(fn) 
set objVars=objSource.GetVariableDescriptions
varctr=0

'Loop through the variables
for i = 0 to objVars.Count - 1
  varctr=varctr+1
  set objTempVar = objVars.Item(i)
  varname=Trim(objTempVar.Name) 
  Set objSourceVar=objSource.Variables(varname) 
  varcontent=objSourceVar.GetRawContent
  'display the variable to check on progress if needed
  'msgbox(varname & " = " & varcontent)

  'update the value of variable in current document
  Set objDestVar=ActiveDocument.Variables(varname)
  If objDestVar is nothing then
    'must need to create variable
    ActiveDocument.CreateVariable varname
    Set objDestVar=ActiveDocument.Variables(varname)
    End If

  objDestVar.SetContent varcontent,true

next 'end of loop 

'we're done, show a message and close down
msgbox varctr&" variables copied from "&fn
objSource.CloseDoc
set objSource=nothing
set objVars=nothing
set objTempVar=nothing
set objSourceVar=nothing
set objDestVar=nothing
End Sub  'end of Copy_Variables

★★★

Make a Copy of a Cycle Group

I sometimes build cycle groups with more than a dozen calculated dimension expressions and labels that are text expressions. It used to irritate me when I needed to make copies of those cycle groups (like when I need a very similar group for a chart with two or more dimensions or a separate group for a graph which may be a little different from a group used for a straight table). Copying the cycle group was a time-consuming chore going back and forth copying and pasting.

Here's a macro you can copy into the module code of a QlikView document that will allow you to easily make copies of a cycle group. I got the code for this macro originally on QlikCommunity and modified it to use input boxes to ask for the cycle group names. It has been been very useful - I usually just copy it into a document temporarily when I need to make duplicate cycle groups and remove the macro afterwards. As always, you should make a backup copy of your document before making major updates or running macros that do updates.

Sub RunCopyCycleGroup
'--------------------
CGName=inputbox("Enter name of cycle group to copy:")
if trim(CGName)="" then 'no entry or cancel
  exit sub
  end if
NewName=inputbox("Enter name for copy of "&CGName&":")
if trim(NewName)="" then 'no entry or cancel
  exit sub
  end if
Call CopyCycleGroup(CGName, NewName)
End Sub
'
Sub CopyCycleGroup(CGName, NewName)
'-----------------------------------
' /* Copy cycle group. OK if NewName exists. */
Set SrcGrp = ActiveDocument.GetGroup(CGName)
Set SrcProps = SrcGrp.GetProperties
Set NewGrp = ActiveDocument.GetGroup(NewName)
If NewGrp Is Nothing Then
  Set NewGrp = ActiveDocument.CreateGroup(NewName)
Else
  Set NewProps = NewGrp.GetProperties
  for i = 1 to NewProps.FieldDefs.Count
    NewGrp.RemoveField 0
    Next
  End If
Set NewProps = NewGrp.GetProperties
NewProps.FieldDefs.CopyFrom SrcProps.FieldDefs
NewProps.IsCyclic = SrcProps.IsCyclic
NewProps.Labels = SrcProps.Labels
NewProps.Present = SrcProps.Present
NewProps.SortCriterias.CopyFrom SrcProps.SortCriterias
NewGrp.SetProperties NewProps
msgbox CGName&" copied to "&NewName
End Sub

I have also posted a macro that will copy a cycle group from one document to another, separate document (search on "Macro to Copy a Cycle Group From One Document to Another").

★★★

Macro Copies Value From a Chart into a Variable

Here's an example of a method that will allow a QlikView macro to look at a value inside a sheet object like a straight table and put the value into a variable. Then the variable can be used in an expression like a text box or small chart. Most of the time, something like this could be accomplished with an expression in the variable that produces the same value as what you see in the chart. But a macro would be useful when duplicating the chart value with an expression would be difficult because of varying sort sequences or calculated dimensions in the chart, etc.

This is the macro code:

Sub Get_XTOTAL
 Application.WaitForIdle
 set cell=ActiveDocument.GetSheetObject("CH45").GetCell(1,7)
 ActiveDocument.Variables("vXTOTAL").SetContent cell.text, True 
 set cell=nothing
End Sub

This example macro is extracting a value from the totals row of the chart where the object ID is CH45 (look in the General tab of the chart properties to find the object ID). The number arguments for the GetCell method are row and column; row 1 and column 7 in this example.
Then the macro updates the value in the document variable named vXTOTAL. When written as in this example, the variable must already exist, so create the variable before executing the macro.
When I use a macro like this, I set up a trigger to execute the macro as needed, usually when a selection changes.

★ ★ ★

Export All Data From All QV Tables to Text Files

Last week one of our clients asked me to provide a method that they could use to export all of the data from within a QlikView document. They had hundreds of archived documents and they wanted to be able to export the data and use it in another application.

“Sure, No problem. I’ll email you some instructions.” I sent them instructions for exporting all of the data from all QlikView tables within a document into tab-delimited text files. This is what I sent to them:

1. Create a new folder on your laptop or server. The computer where you do this must have QlikView installed.

2. Copy the QlikView .qvw document file into your folder. This will make sure you don't accidently update the original document.

3. Double-click on the .qvw file to start up QlikView.

4. From the menu, select File->Edit Script. This opens up the Loadscript Edit Window.

5. At the very top of the loadscript, before the first line, copy and paste in these lines:

/* Export all QlikView tables to tab-delimited files */
if IsPartialReload() then
for ix= 0 to 100
let tn=trim(tablename($(ix)));
if len('$(tn)')<1 then
exit script;
end if
Store "$(tn)" into "EXPORTED_QV_$(tn).TXT" (txt, delimiter is "\t");
next ix
end if
exit script;
//
6. Close the Loadscript Edit Window by clicking the OK button in the lower right corner of the window.

7. From the menu, select File->Partial Reload

8. You don't need to save the document, but if you do save it, do not copy it back to the original location.

If everything works correctly, each of the QlikView tables will be written out as tab-delimited text files into the folder. For example, if the QlikView document contains a QlikView table named HISTCUST then it would write out a text file named, EXPORTED_QV_HISTCUST.txt. The first record will contain the field names.

Note the syntax for specifying the delimiter character. If your Store command simply includes (txt) without specifying the delimiter you will get comma-delimited text files. Use the file specification, (qvd), to get QlikView QVD files.

I would've preferred a more elegant piece of code using a Do While loop but I had trouble getting the test to run correctly and I had limited time. The code above should work fine for documents with under 100 QlikView tables.

* * *

Browse-for-File Macro Button

Several times I’ve created QlikView reports that must read data from a file but the name of the file can vary and must be supplied by the report user. Often this is a file that will be used by the loadscript so the user first clicks a macro button that runs a browse-for-file dialog and then the user clicks the standard Reload button.

Here’s an example of a macro that borrows the browse file dialog method from the Microsoft Excel object model. This macro should work on any PC that has MS Excel installed (it does not actually start Excel). You can copy the code from here and paste it into your QlikView report module code:

Sub Browse_for_File
'------------------
' Get the file pathname
Set oXL=CreateObject("Excel.Application")
f_name=oXL.GetOpenFilename("All Files (*.*),*.*",,"Select file",False)
If f_name="False" then
  'user cancelled out of dialog box
  Set oXL=nothing
  Exit sub
End If
'store file pathname in the file_pathname variable
ret=ActiveDocument.GetVariable("file_pathname").SetContent(f_name,false)
Set oXL=nothing
End Sub

This macro stores the file pathname for the file selected by the report user into a document variable named file_pathname (create the new document variable before running the macro). When I create a report that uses this macro I usually also put an input box in the report that shows the file pathname stored in the variable and allows the user an alternative method for specifying the pathname.

A QlikView document example of this technique, named BIG_Text_File.qvw, is available here – http://finmagic.0catch.com/ Sorry about the ads and popups – just click on the specific file download.

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.