QlikView Command Line and Automation

This posting is for the more technically inclined among you and developers who work with reports that must reload automatically.

You can run QlikView from the command line. You've probably created shortcuts on your desktop to various useful QV reports. In that kind of shortcut the command is simply the complete pathname to the report file. That will also work in a .bat or .cmd file; just enter a usable pathname to the report file and Windows will open QlikView for the report.
More interesting things can be done with a command that includes the pathname to the QlikView program followed by a pathname to the report file. This command line syntax allows you to enter switches or parameters. The command line syntax is covered pretty well in the reference manual. For example: If the pathname to your QlikView program is c:\Program Files\QlikView\QV.exe and the report you want to open is c:\reports\Inventory.qvw then this line will do a simple open of the report in a shortcut or .bat or .cmd file:
"c:\Program Files\QlikView\QV.exe" c:\reports\Inventory.qvw

You may or may not need the quotes around the pathname for QV.exe or for your report file. Windows usually wants to have the quotes for any pathname containing a blank or special character.

If you add a /r switch (notice the front-leaning slash) then the command will open the report, run the reload function (executing the loadscript), and then save and close the report:
"c:\Program Files\QlikView\QV.exe" /r c:\reports\Inventory.qvw

A /rp will do something similar except it runs the partial reload function.

The /l switch (that's a lower case ell) will open the report, run the reload function and leave the report open. This one can be useful as a desktop shortcut:
"c:\Program Files\QlikView\QV.exe" /r c:\reports\Inventory.qvw

A /p switch will open the report, run a partial reload and then leave the report open.

The /v switch can be used to pass a document variable value into the report. It can be used either with or without the switches described above. The document variable might be used in macro module code or in the loadscript. In our example if we want to reload the QV report and pass in the document variable batch_flag with a value of 1 then we'd use a command like this:
"c:\Program Files\QlikView\QV.exe" /r /vbatch_flag=1 c:\reports\Inventory.qvw

[The previous line and all of the previous command line examples are intended to be a single line even if your browser is breaking them into two lines]

We often use a command like that for reports that work differently when they are executed in batch versus being opened by a user.

My personal favorite way to use the command line syntax is from a VBScript file (.vbs script) on the Windows PC. VBScript allows me more flexibility in automatically figuring out which report file to run and what kind of document variable values should be passed in. From the VBScript file the syntax might look something like this: (using the same example)
Set objShell = CreateObject("WScript.Shell")
objShell.Run """c:\Program Files\QlikView\QV.exe"" /r /vbatch_flag=1 c:\reports\Inventory.qvw"

[Set objshell... is a single line and objShell.Run... is the second line no matter how your browser is breaking them up]

In addition to filenames using drive letters you can use network file names like this: \\canserver\projects\reports\Inventory.qvw

VBScript in Loadscript and Module

The language used in both the QlikView loadscript and in the module code (macros) is modified VBScript. You can easily find information about VBScript commands and syntax through Google. For example, to find information about msgbox prompts and syntax enter this in Google:
vbscript tutorial msgbox

The best thing about Bookmarks

Many QlikView users don't make much use of the Bookmark feature. The Bookmark feature lets you save your current selections and give it a name so that you can easily recreate those selections later.

I have found the most useful part of bookmarks is that they can be exported as a little file. Then the little file can be imported back in to the report tomorrow or it can be imported into a different report. We have quite a few reports that include the field name, ITEM, which is a product identifier. Quite often, I have a selection of several ITEM values from one report that I'd like to see as a selection in a different report. I can just make a bookmark, export the bookmark, and then import the bookmark file into a different report and have those same ITEM values selected. If there are any field selections from the first report that don't make sense in the second report because the fields so not exist they are just ignored.

Sometimes a co-worker will send me a spreadsheet of data that requires further analysis. I can quickly import that spreadsheet into a new temporary QlikView report, select the ITEM values to be investigated, make a bookmark and export it. Now, I can import that bookmark file into one of our comprehensive QlikView reports and review data for the ITEM values from the spreadsheet.

Copying a Chart From One Report to Another

Sometimes you want to copy a sheet object from one QlikView report to another. You might have two reports; say one for the finance department and a separate report for the supply chain department that both load similar data from the database. If the supply chain analyst develops a really great chart for the supply chain report and the people in finance could use it then you could copy the chart from the supply chain report to the finance report. You could always just recreate the chart from scratch on the new report of course, but it is easy to copy the chart from one report to another using Windows clipboard. Here’s how it could be done using our example:

• Open both reports


• Make a back-up copy of the "finance report" (the one you are going to change) by doing a save-as under a different name


• Now, go to the supply chain report that has the cool chart. You could use the Window menu to select the other report. Click on the chart so that the chart is selected (when the chart is selected the title bar at the top of the chart changes color).


• Select Edit->Copy from the menu


• Switch over to the report you are going to change, the "finance report" in this example. Make sure you are looking at the finance report and select the tab where you want the chart to appear


• Select Edit->Paste from the menu and the cool chart will get pasted into the finance report. It will appear in approximately the same position on the sheet that it occupied on the original report.


• Finish up by moving the chart to the position you want, make any required changes to chart properties (like data field names or title, etc.) and save the changed report

You can use this method to past a sheet object into several different reports or paste the same sheet object into multiple tabs on the same report. I use this method all the time when developing a new report with multiple tabs.

Another method that can be used to copy a chart within a single report is to click on the chart, select Properties, and then select Clone. A copy of the chart will appear just slightly offset from the original one. You can use your mouse to drag the cloned chart either to a new position on the same tab or drag it to one of the other tabs at the top of the report and, when you see the curved arrow appear, release the mouse button and the chart will now be on the other tab.

Field names and compressing data

One of the cool things about QlikView is the way it can store enormous amounts of data in the report file. It can load many millions of rows of data into a report file that might only be one or two MB in size. It compresses data so well that I seldom zip a report file before sharing it since the data is stored so densely in the QV report that zipping the file doesn't give much of a savings. If you are working with loading very large tables into a report be aware that part of the QV strategy for compressing data depends on not actually storing duplicate values in a field. If the report loads sales order data, for example, and the data contains customer name then there may be many duplicate values in the data for any customer who has ordered many times. QV will only store the customer name once and then keep track of where that customer name is used again as subsequent rows are loaded.

This mechanism means that if you load two different tables from your database that contain customer name into two different QV tables and if the field name is the same for both QV tables then each customer name value is only stored once. But, if you give the field a different name in each QV table then the program cannot know that it is the same data and it must treat each field separately and many customer name values will be stored twice. This is really only a consideration when working with enormous tables where the sheer size of the tables is affecting memory utilization of the report or the size of the report file.

Remember that QV relates or joins the tables together based on the fields they have in common. Two QV tables that both contain the field Customer_Name will be related based on that field. So, sometimes making the field names in the tables the same to be more efficient with memory utilization will not be possible if it causes two tables to be joined when they shouldn’t (the joining of tables also consumes memory space). And the field names should only be the same if the field data is truly the same. Customer number from your company’s database is not the same as customer number from another company’s database. But, customer number from your company’s 2006 sales data is probably the same as customer number from the 2007 sales data.

Changing Daily Data to Weekly in a Chart

I got a call earlier today from an analyst who had a QlikView report loaded with detailed shipment data. The main chart on the report showed shipment totals by day but the analyst needed the chart to show weekly quantities -- "is there a way to make it show the weekly data?"

"Well, you're in luck. Not only can it be done but you can do it yourself in a few minutes." Follow these steps:

• Open the QlikView report and save a copy of your report under a different name by selecting File->Save As from the menu (just in case I give you bogus instructions that don't work... it happens).


• Right-click on the chart that shows the daily data. Select Properties and select the Dimensions tab. It might look like the diagram above. [Click on that diagram to make it bigger and get a better view]


• You'll see a SHIPDATE entry in the Used Dimensions window. Click on it and then click the Remove button


• Now, click on Add Calculated Dimension... the Edit Expression window opens up. We're going to add an expression that yields a weekly date to replace SHIPDATE. Type in this expression:
  WeekStart(SHIPDATE)


• Click OK to close the Edit Expression window


• Give our new dimension a name- type WEEK in the field name box. See how it might look in the diagram below.


• Click OK to close the Chart Properties window

Now the chart shows data by WEEK instead of SHIPDATE. This same method can be used for other types of date conversion functions or most other kinds of numeric or text string calculations. It lets you use a chart dimension even though the dimension data doesn't actually exist that way in the loaded data.

More you can do with Partial Reload

See the previous posting about removing tables. In the same way that you can enter a Drop Table command to remove a QlikView table from an existing report, you can enter these commands which will affect the existing report already loaded with data:

• To remove a field from every QlikView table in which it appears, enter Drop Column;
  For example, for privacy reasons you might want to get rid of social security number data in your report before sharing it with your team. You might enter:
  Drop Field Customer_SSN;


• To remove a field from specific table(s), for example, you might enter:
  Drop Fields Customer_Name, Customer_SSN from CUST_MSTR_TABLE, ORDER_HDR_TBL;


• To change the name of a column or field in every table in which it appears enter Rename Field; (remember that the field names are how QlikView relates or "joins" the tables together so this one affects that table relationship).
  For example, you might enter:
  Rename Field domestic_custno to North_America_Customer;


• You can change the name of an existing table. For example, you might enter:
  Rename table domestic_sales to North_America_Sales;

All of these commands can also be used in the loadscript during a normal load but they can be especially useful for fixing issues when used with an existing report that is already loaded with data. Remember to put them at the top of the loadscript, add an Exit Script; command and then execute the commands with the Partial Reload. The rename commands can be used with a mapping table if you think you might need more powerful renaming capabilities. Refer to the Help Text or Reference Manual for info about how to use a mapping table for renaming fields and tables.

How to remove an existing table

For this first blog entry we'll talk about something simple although maybe not a beginner's task. How do you remove a QlikView table from an existing report that is already loaded with data? There's a variety of reasons you might do something like that: to remove a large table and reduce the size of the report, maybe the data in one table is incorrect or causes incorrect results in the charts, or maybe you intend to share the report with Marcy in Finance and she just doesn't need to see the sales forecast. Whatever the reason, it is fairly easy to remove a table.

First, make a copy of the report you are going to change so that you can restore the report in case something goes wrong or you make a mistake (it happens). Open the report, then select File->Edit Script from the menu. It will open the loadscript edit window. Insert a few blank lines at the very top. Now type in a drop table command for the table you want to remove. For example, if you want to remove the NEW_ORDERS table then type in
DROP TABLE NEW_ORDERS;
EXIT SCRIPT;

Don't forget the semi-colons at the end of those lines. It might look something like the picture up above.

After you type the new lines in the loadscript, review the lines to make sure everything is spelled correctly and click on the OK button to close the edit loadscript window and then select File->Partial Reload from the menu. The partial reload choice will execute your new loadscript lines down to the Exit script line and then stop. The NEW_ORDERS table has now been removed from the report. If you ever expect to reload the report again you should edit the loadscript again and remove the new lines you just inserted. Then click OK to close the edit loadscript window and Save the report (or do a Save As to save the report under a different file name).