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.

Loading All of the Files from a Folder

It's pretty easy to load all of the files from a folder into QlikView. For example, if you have a number of Excel spreadsheets in the f:\Finance\monthly_summaries folder and each spreadsheet contains a tab named EOM_SHEET and the column names on that tab are consistent in each spreadsheet file then these lines in your loadscript will load all of the spreadsheets into your QlikView report:

Directory f:\Finance\monthly_summaries;
For each ExcelFile in filelist ('*.xls')
EOM_HISTORY:
Load * From $(ExcelFile) (biff, embedded labels, table is [EOM_SHEET$]);
Next ExcelFile;

Note that this looping through the files of the folder will only use filenames that end with a .xls extension. So, any other documentation or data files in the folder will be ignored. The spreadsheets can contain other worksheet tabs too -- only the worksheet tab named EOM_SHEET will be loaded into QlikView. Hundreds of spreadsheet files can be loaded fairly quickly this way. As always with QlikView object names spelling counts and it's case-sensitive, e.g. EOM_SHEET is not the same thing as EOM_Sheet. If you're worried that not all of the spreadsheets use the same column headings then it might be best to code each column heading as a specific field name in the Load statement. That will make QlikView call it an error if the column headings in any of the spreadsheets don't match the expected field names. The error is usually better than loading inconsistent data. The Directory statement and Load statement can also make use of variables to make them more flexible to handle folder and file naming conventions. The load statement, of course, can be made to fit most situations with Where clauses and Group by options.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
In a comment, Guido correctly pointed out that this syntax achieves the same result and it's simpler:
EOM_HISTORY:
Load * From f:\Finance\monthly_summaries\*.xls (biff, embedded labels, table is [EOM_SHEET$]);

Check out the other comments to this posting.

Fixing the Field Name in an Exported Bookmark File

Last month, I wrote about how useful bookmarks are because they can be exported as a file and then imported into different QlikView reports (Click here to read it). A problem you might run into from time to time is trying to use a bookmark file where the field name in the original report is different from the field name in the report where you want to import the bookmark. It's the same data but perhaps the field name in the original report was spelled differently. This kind of thing happens all the time when data is loaded into QlikView from spreadsheets. You can edit the bookmark file to fix that problem.

Here's an example:
Imagine that your co-worker, Kavya, in the Finance department has a QlikView report with all of the sales districts selected that will be used for the new product rollout. You could really use that bookmark for your project. She makes a bookmark of the selection and exports it and emails you the bookmark file, named Rollout_districts.qbm. But, after you import the file and click the bookmark name and nothing is selected, you discover that Kavya named the field in her report sls_district and in your report it is named Sales_Districts. If either Kavya's report or your report could easily be reloaded then you might be able to fix the field name during the reload - OR - you can quickly edit the field name in the bookmark file and make it usable.
Find the file (in this example, Rollout_districts.qbm) in Windows Explorer. Right-click on the file and select Open With->Notepad. The bookmark file is a kind of text data known as XML. It's ok if you never heard of XML; you don't need to know anything about it for this process. In the Notepad window select Edit->Replace... and when the Replace window opens type sls_district in the Find What: field box and type Sales_Districts in the Replace With: box (*See the diagram at the top of this posting - click on it for a better view). Now, click the Replace All button and then click Cancel to close the Replace window. Click File->Save As and save the bookmark file under a different name (just in case you need the original file again). Don't edit or change anything else in the bookmark file. Finally, import the new bookmark file into your report and you'll have the selections just as you wanted.

For this process to work the way you want, of course, it must be the same kind of data in the fields in both the original report and the report being edited. You can't change a bookmark file of country names into a bookmark for movie titles just by editing the field name.

Automatic Concatenation Watch-Out

This is a mistake I make every once in a while and it always puzzles me for a few minutes before I figure out what happened. In the loadscript, if you load data from several sources and use exactly the same field names then QlikView will automatically concatenate the data from the second source onto the table created for the first source. It does this unless you specify the keyword NOCONCATENATE.

Here's an example: Imagine that your loadscript loads data from a customer master table and following that you write two load statements to create a small table of just the Club customers and a second table of just the Military customers. Maybe you intend to use the small tables in a Where Exists statement further down in the script. You might write the loadscript statements like this:

CLUB_CUSTOMERS:
Load customer_no
Resident CUSTOMER_MASTER
Where customer_type = 'CLUB';
MILITARY_CUSTOMERS:
Load customer_no
Resident CUSTOMER_MASTER
Where customer_type = 'MILITARY';

That's not going to work the way you intended. There's no error or warning but after the loading you'll discover that there is no MILITARY_CUSTOMERS table. What happened is that QlikView concatenated the customer_no values from the second load statement onto the CLUB_CUSTOMERS table. It did that because the field names in the second table are exactly the same as the field names from the first table. It will do this even if the two load statements are not one after the other like in my example but even if there are other loadscript statements in between. The automatic concatenation feature is probably useful in quickly assembled loadscripts but it can cause trouble for more complex applications. You could change the field name in the MILITARY_CUSTOMERS table to be different, like customer_no_military and the automatic concatenation won't happen. OR, and this is my preference, add the NOCONCATENATE keyword to the second load statement like this:

MILITARY_CUSTOMERS:
Noconcatenate Load customer_no
Resident CUSTOMER_MASTER
Where customer_type = 'MILITARY';

Then you will end up with the two small tables that you intended.

Another keyword, CONCATENATE, is sometimes useful when you want to load two sets of data where the fields are not exactly the same into the same table. When you use this one though it will concatenate the data being loaded to the previous table in the loadscript.

Reducing Report Size

In the previous posting I showed how a table in an existing report could be changed to make the table smaller (Click here to read it). In a comment to that posting, Rob Wunderlich correctly points out that a much simpler way to make the report smaller is to select the data you are interested in retaining and then, from the menu, select File->Reduce Data->Keep Possible Values. Then save the report under a different name in case you ever want to refer to the original. Thanks for the comment, Rob. That technique is great for permanently removing data from a report for any reason, not just to make the report smaller (e.g. removing old data, sensitive, irrelevant, unneeded, or erroneous data).

The method shown in the previous posting for changing a table in an existing report is still useful for changing field values, adding or joining new fields, etc. Someday I'll have to edit that posting to a more relevant example.

Change a Table Without Reloading Report

Last month I wrote a posting about interesting things you can do in the loadscript with a Partial Reload. Here's another:

I had a large report that we needed as an example for a presentation but it was just too large. The original database it loaded from is no longer available, so I needed a way to make the report smaller without reloading it. It had one large shipment data table in it and I decided to change the the table to only include two shipping location codes which would make it much smaller.

See the picture above (click it for a better view). I inserted this code at the top of the loadscript:

/* Example loadscript fragment to adjust a table */
/* Put this code at the top of the loadscript and */
/* then run a Partial Reload. */
/* Remove this fragment after using it. */

//Rename the table we want to change
RENAME TABLE HIST TO HISTX;

//Rebuild table with a WHERE clause
HIST:
ADD NOCONCATENATE LOAD * RESIDENT HISTX
WHERE (LOC='001099') or (LOC='011098');

// Drop the original table
DROP TABLE HISTX;

//so that we don't fall into the regular loadscript
EXIT SCRIPT;

Then, I saved the report file under a different name (just in case) and ran a Partial Reload. The Partial Reload executed the commands at the top of the loadscript without removing all of the other data and made the report file a lot smaller and the memory or RAM requirement for the report smaller too. Unless you know that you'll never need to reload the report again you should remove the loadscript lines you added at the top.

Don't forget the ADD or the NOCONCATENATE keywords or you'll find that the table you wanted to rebuild is missing after you run the Partial Reload.

Checking for Loadscript Errors from the Macro code

If you are using the macro code Reload method to load data into your QlikView report you can also easily check for any loading errors. Here's an example of how that could be done. Add a small subroutine to the macro code that can check the system variable for error count like this:

Sub Check_for_errors()
'---------------------
'See if any load script errors occurred and report them
alarm_flag = 0
Set objvar = ActiveDocument.Variables("ScriptErrorCount")
If trim(objvar.GetContent.String) = "0" then
  'No error - hooray
  Exit Sub
  End If
alarm_flag = 1
'Here you can add any other error notification
End Sub

And then, in the subroutine that reloads the document code add an IF statement after the .Reload method something like this:

ActiveDocument.Reload
Check_for_errors 'call Check_for_errors subroutine
If alarm_flag = 1 then 'an error happened in the loadscript
  ActiveDocument.CloseDoc
  ActiveDocument.GetApplication.Quit
  '***Exit Function or Exit Sub
  End If

In the reports I've developed there is error checking every step of the way and any error causes a notification file to be written out. If you have an email client program on the computer where the report is reloading then consider using an email notification to the report users or to a technical support person. I've sometimes sent a small notification email message to my cell phone (My favorite message is "Lassie, go for help!") so that I can be notified immediately with the bad news.

Checking for errors when a report reloads automatically

In a recent posting (Click here to read it) we discussed ways to run QlikView from the command line as part of an automated script process. If you run the reload process automatically then you're faced with an issue: what should happen if an error occurs during the reload? Even a report that has reloaded successfully dozens of times before can run into an error if a file is missing or the database is down or if someone changed the title on a spreadsheet column (that's the one I seem to trip over most often).
If you're going to sit at your desk in front of the computer and watch it run then you don't have to do anything -- any loadscript error window that opens up can be clicked and the report can be cancelled if necessary. But, if the report is going to load unattended without anyone watching then consider these actions:

Check the Generate Logfile box on the General tab of the Document Properties window (select Settings->Document Properties from the menu - see the picture above). This is often a good idea whether you are reloading the report automatically or not. It causes QlikView to generate a text file audit trail of what happens as the loadscript runs. It captures the number of rows returned from queries and which branch an IF statement takes, it shows the expanded value of document variables that are used in commands and expressions and it shows the date and time for each command which allows you to analyze the slow-running parts of the loadscript. The logfile will be named just like the report file but with a ".log" added to the end. For example, if your report file is named Prior_Yr_Sales.qvw then the log file will be named Prior_Yr_Sales.qvw.log and it will be stored in the same folder as the report file. Open the log file with Windows Notepad or Wordpad. You can search for errors in the log file by using the Notepad or Wordpad Search function and search for Error:

For a report that must reload unattended (with no human folk watching) add this line to the top of your loadscript:
  SET ErrorMode=0;
That will tell QlikView not to open a loadscript error window when an error occurs (which would wait for someone to click OK). Instead the loadscript will go through all of the commands and do the best it can to execute all of them.

From your .bat or .cmd script you can have the script search for any lines containing the text Error: and perform an action if it is found. Here's an example:
rem Initialize the log file with xxx
echo xxx > c:\RPTS\Inventory.qvw.log
rem Now, run QlikView and reload
"c:\Program\QV.exe" /r /vbatch_flag=1 c:\RPTS\Inventory.qvw
rem Look through the log file and write
rem a note to Joe if we find an error
Type c:\RPTS\Inventory.qvw.log Find /c "Error:"
If errorlevel 1 echo %date% %time% "Error occurred" >> c:\Notify_Joe.txt