All posts by Rob Wunderlich

Initial Selections and Current Selections

I’ve posted in the past about setting “dynamic” (based on date) initial selections. The easiest method from the Developer perspective is to apply search expressions, save them in a bookmark, and apply the bookmark as an OnOpen action. The bookmark may also be selected by the user at any time to return to initial selections.

For example, to select the current year and month:


This selects the correct Year and Month. However, the current selections display is poor.  The search expression, rather than results, is displayed in the Current Selections box.

Alternatively, those same dynamic selections can be achieved via Actions assigned to the Document OnOpen Trigger. Using Actions will present a cleaner current selections view. Here are the two Actions we should assign to OnOpen to achieve the same selections — but with a better looking current selections display at the end.

The Current Selections Display will now be much more comprehensible to the user.

Can we provide a way for the user to return to the dynamic initial selections by utilizing a bookmark? Yes we can. Create a new bookmark, assign it an appropriate name (like “Current Period”) and note its ID (for example “BM02” — from the Bookmark, More menu). Add one more action to the OnOpen trigger.

The selection values will be calculated and the bookmark updated with the new values each time the document is opened. 



Correct Time Arithmetic

Each time value in Qlikview has  both an external display string value and an internal numeric value. The numeric value ranges from 0 to 1 and is calculated as time/24 hours.  For example, 12 noon is represented as 0.50. 
Rounding during arithmetic can yield time values that share the same same external string but have a different underlying numeric value. The numeric value is used to group values in listboxes and chart dimensions. This is why you can sometimes see the time “01:00” display twice in a listbox or chart dimension!
Both of the time values below will display as “8:00:00 AM”. But they will not group together in the same dimension bucket on a chart.

8/24 = 0.33333333333333
MakeTime(8) = 0.33333333334303
I’ve seen a number of methods to do time and interval calculation. Some methods will have problems with certain time values. 

Look at some examples below and how they would match up with QV times, such as those read from databases or created by QV time functions. 

MakeTime(8) =     time#(’08’, ‘hh’)     True
MakeTime(8) = 8/24 False
MakeTime(8) – MakeTime(1) = MakeTime(7) False
MakeTime(4) – MakeTime(1) = MakeTime(3) True
time#(’08’,’hh’) – 1/24 = time#(’07’,’hh’) False
time#(’04’,’hh’) – 1/24 = time#(’03’,’hh’) False
time#(’08’,’hh’) – time#(’01’,’hh’) = time#(’03’,’hh’) False
time#(’12’,’hh’) – MakeTime(3) = time#(’09’,’hh’) True
time#(’08’,’hh’) – MakeTime(1) = time#(’08’,’hh’) False
time#(time(MakeTime(8) – MakeTime(1))) = MakeTime(7) True

The only reliable and 100% consistent method is the last line. The method is: use Qlikview functions to represent times and convert the result of calculations to time strings and back to time numbers again.

Breaking down the  example: 

1. MakeTime(8) – MakeTime(1) give us a display value of  7:00:00 AM. But as you can see previously in the table, it’s internal value is not exactly equal to MakeTime(7) — the value Qlikview considers to be 7 AM.

2. Convert the value of the calculation to a string using the time() function. The result is “7:00:00 AM”.

3. Convert the string into a numeric time using the time#() function. We now have the QV value for 7 AM which is equal to 7 AM read from a database or generated from any QV time function.

The same problem and solution applies to Timestamps and Intervals.

What we really need is a new “AddTime()” function, similar to the AddMonths() function. I sometimes create a variable function in my script to perform this function.

SET AddTime=time#(time($1 + $2));

Then I can use it in script or charts as:




The Case Against Caption Icons

I avoid using Caption Icons in sheet Objects. I prefer to teach users to right-click to access the same functionality. In V10, there is also the option to use a  “Menu” caption icon that provides the same options as right-click.

Here are my arguments for not using caption icons that duplicate the functionality available in right-click.

Note: I realize there are some limitations in the Ajax client prior to V9 that may require icons. My comments are directed at V9+.

  1. The redundancy and visual clutter waste screen space and distract from the data. If you are a fan of Edward Tufte’s work, you’ll recognize this as “low Data-ink ratio” and “Chartjunk”.
  2. They deemphasize icons that may be unique and important to a specific chart. These are things you want the user to notice, for example Help or Fast Change.
  3. They force the designer to display a Caption even when a Caption is not desirable for the layout.
  4. They keep users ignorant of the full power available, leading them to believe that they are limited to the functions offered by the displayed icons. Have you ever received a change request like “Please add the export to Excel function to chart X so we can export it.”?
  5. Also on the ignorance theme, users don’t become aware of new functionality if you don’t add the new icons.
Comments and counter arguments welcome.

My Top Two Feature Requests

There are a couple of missing features in QV that cause me to often bump my head against the ceiling. I have a great idea but then ouch — can’t make QV do that. Below are my top two feature requests. With all the cool, advanced, sophisticated ideas floating around out there, you may be surprised that my requests appear so basic.

#1. An escaping or alternate syntax for string literals that contain single quotes. I frequently use script subroutines and variables with parameters to bring discipline to my QV projects. Parameters are passed as strings and variables are sometimes themselves function calls that contain quoted parameters. Nested routines make the problem even more complex.

There is also a problem using SET and LET in script to define reusable expressions. The defined values often contain quotes.

Quoted strings cannot contain quotes, so I resort to all kinds of workarounds — & chr(39),  proxy characters for quotes and using fields when I would really prefer variables. All of these are complex workarounds that make my code resemble the Twister game.

#2 Grouped Objects. So many great screen objects available. Wouldn’t it be wonderful have a couple of charts, a listbox and an explanatory text box minimize & restore as a group? I’ve blogged about this in the past if you want to read more on the topic. So much could be done in the UI with the addition of object groups.

I’ve dropped both of these feature requests in various Qliktech “suggestion boxes” over the years. I have no idea if they will ever  be implemented or not. I frequently hear other users asking for #2, and I occasionally meet someone who shares my desire for #1.

What are your top two feature wishes?


Document Compression

Today I offer up a discussion of Qlikview “compression”. That is, the Qlikview features that make overall data get smaller, and in some cases, larger.

Should you care? In most cases no. But understanding what “knobs you can turn” can be a useful tool for capacity planning and application tuning. Let’s look at the practices and parameters that affect data size.

 Script Execution:  Data read from sources – such as database tables – are read in to memory (RAM) by the script execution (reload) process. Duplicate values are reduced to the unique set of values for each column. A “Gender” column has only two values – “Female” and “Male”, so the storage required for this column is minimal compared to a column that has a wide range (cardinality) of values such as a timestamp.  This is not really “compression” but rather what I call “de-duplication”.

The ratio of database storage to document storage is dependent on the data content as well as the use of common script techniques like separating timestamps into date and time fields. A typical database to document ratio is 10:1. For example, 2GB of database tables might require 200MB of document RAM.

QVW write to Disk: After reload, the Qlikview document (data tables and screen objects) is written from RAM to Disk as a *.qvw file. If compression is set on (default) for the document, the qvw will be compressed as it is written to disk. The compression results will vary depending on data content, but is typically in the range of 2-5 times. For example, a document that requires 200MB of RAM will require somewhere between 40MB and 100MB of Disk storage.

If compression is set to “None”, the document will be written to disk in the same format it existed in RAM and will occupy the same storage on disk as it utilized in RAM.
The Compression option for each Document is set in the Document Properties, General tab. The default compression for new documents is defined the User Settings, Save tab.

The compression option will of course impact the amount of disk storage used. But it also affects the amount of time it takes to read or write a qvw. I find that for most documents, an uncompressed document will write and read significantly faster than a compressed document. Some documents, especially large ones with high compression ratios, will read faster if compressed. The other factor is speed of the disk being used – local disk or network disk.

I typically do my development with compression off and then do a timing test with both options before migrating to the server.

QVW read from Disk: The *.qvw is loaded to RAM by a developer or on the Server by a user session. The amount of RAM required is the uncompressed size, regardless if compression was used to write the *.qvw to disk.  As discussed in the previous section, my experience is that uncompressed documents read from a local disk typically load up faster, but this is not always true and is worth testing on large documents.

­What is the compression factor for QVD files?

A QVD file contains the physical representation of an in-memory Qlikview Table. This “RAM image” format is what allows an optimized QVD load to be so quick. The physical blocks of disk are read directly into Qlikview RAM, “ready to go”. Because QVD is the RAM image, there is no compression.

A QVD read with an optimized load will require the same RAM size as its size on disk (1:1). A QVD read with an un-optimized load may require significantly more RAM, due to some numeric fields being converted to strings. The expansion is typically about 2:1 but varies considerably.

Here is a summary of the various “compression points” and typical results.
Source DB
Raw Data
Source DB
Document RAM
Data de-duplication
Document RAM
QVW Disk
Save Compression=High
Document RAM
QVW Disk
Save Compression=None
QVW Disk
Document RAM
Save Compression=High
QVW Disk
Document RAM
Save Compression=None
Document RAM
QVD Disk
QVD always uncompressed
QVD Disk
Document RAM
Optimized load
QVD Disk
Document RAM
Non-Optimized load

If your documents are small and you are not experiencing performance issues, worry about none of this.

Compressed documents occupy less disk space and their smaller size makes them easier to manage for moving, backup, etc.

If you are trying to get a document to load faster, try turning off document compression and benchmark your results. Consider the type of disk when making this decision. Compression may more important in a network storage environment where reducing the amount of data transferred is a significant performance factor.

It’s important to understand that the document compression option has no impact on RAM usage. It only impacts the amount of data read and written to disk.

QVD Questions and Answers

On Feb 2 I presented a live webinar titled “Understanding & Best Practices with  QVD Files”. You can download the slides as a pdf. 

Many questions were submitted during the presentation, too many to answer at that time. I’ve tried to answer those questions and others below. If you have further questions about QVDs, post a comment here or  in the QlikCommunity Forum. 

Q: ­Does the QVD data get stored in an RDBMS like Oracle, or is it in a file system? ­
QVD files are stored in the file system.
Q: ­what is the compression factor for QVD’s­?
QVD files are stored uncompressed. A QVD contains the physical representation of an in-memory Qlikview Table. This “RAM image” format is what allows an optimized QVD load to be so quick. The physical blocks of disk are read directly into RAM, “ready to go”. Because QVD is the RAM image, there is no compression.
Q: ­Can we trace back QVD to its source?
As of QV10SR2, the XML header in a QVD file contains the name of the QVW that created the QVD as well as file sources and database connections/SQL statements.
Q: ­Why is sorting not possible while loading QVD?­
Sorting (ORDER BY) is only possible with Resident (already in memory) files. Sorting is not possible when reading from files.
Q: ­Could you go over again the concept of “forcing” un-optimized load for the MAPPING function, respective to the qvd?­
MAPPING tables may be loaded from a QVD, but it must be an un-optimized load (this is sometimes called “unwrapping”).
MAPPING LOAD F1, F2 FROM sometable.qvd (qvd);
The above mapping table will be created but it will appear to be empty when used in MAP USING or ApplyMap().  No error, just no resulting mapping.  One workaround is to create a condition that will cause an un-optimized load.  We want all the rows, so we create an always-true condition that will return all rows.
MAPPING LOAD F1, F2 FROM sometable.qvd (qvd)
WHERE 1=1;

Note: In QV10+, the MAPPING prefix will trigger an unoptimized load. The 1=1 trick is not necessary.

A corollary to this is that the target of a mapping operation cannot be an optimized QVD.
MAP Country USING MyMap;
// Optimized load, Country will not get mapped.
LOAD Customer, Country FROM customer.qvd (qvd);
Q: ­The use of the Where 1=1 is something that will be good for mapping fields in the future or is possible that qlikview will determine that where 1=1 will allow optimization?­
Good question. We use WHERE 1=1 to force the un-optimized load required by MAPPING LOAD. I’m hopeful that if Qlikview were changed to consider 1=1 as an optimized load, they will also recognize that MAPPING LOAD should be non-optimized.
(Note: In QV10, MAPPING LOAD is automatically non-optimized).
Q: ­How would you handle the need to load multiple models (ie multiple qvws)?? I don’t think you can do multiple binary loads, so what do you recommend.­
You can generate QVDs from each model and then load all the QVDs to form the larger model.  You can generate all QVDs from a qvw with a simple loop. You can add this code to each of your model qvws.
FOR i = 1 to NoOfTables()
  LET vTableName = TableName($(i)-1);
  LET vOutfile = ‘$(vTableName).qvd’;
  STORE [$(vTableName)] INTO [$(vOutfile)] (qvd);
Q: ­How are QVD refresh scheduled?­
QVDs are created by script in a QVW executed by the reload process. Schedule the reload as you would the reload of a user facing qvw, using the Qlikview Enterprise Management Console (QEMC) or a batch file.
Q: ­Is QVD Optimized load really worthwhile since it is fairly limited? In other words, should we load data to the memory striving for QVD optimized and then work with the memory tables within the script?­
Optimized vs non-optimized load has two impacts: Load duration and Server RAM usage. If your application is relatively small or you do not have concerns about the impact, don’t spend time trying to maintain an optimized load. Some of the script techniques used to maintain optimized can make your script harder to follow.
If, for a given document, you have concerns about load duration or RAM usage, then making the effort to maintain an optimized load would be worthwhile.
Q: ­Can a QVD be accessed from a AS400 DB2 database to get some data?­
Nothing but Qlikview can read from QVDs, so no, DB2 cannot read directly from a QVD.
In the same script that creates the QVD with the store statement:
STORE mytable INTO mytable.qvd (qvd);
You can also create a CSV copy for other consumers:
STORE mytable INTO mytable.csv (txt); 
The csv file can be read by any number of programs, including an ODBC text driver or a bulk database loader. You can use QV to do the ETL and then push csv files back into a Data Warehouse, using something like SQL Server DTS or other data pump.
Q: ­I’m pulling data from a database over a slow WAN link.  Would using a qvd speed this up?  If so, would the qvd file reside on the same side as the database or at the end of WAN Link (client side)?­
Using QVDs could speed up your overall process by allowing multiple reloads to load from the qvd instead of going to the database over the slow WAN link. The QVD should live at the client end of the link – where the qvw is reloading.
Q:  If the data source is constantly changing (such as portfolio management software) can we refresh qvds frequently? Will this overburden the process?
QVDs may be refreshed frequently. Exactly how frequently depends on your data volumes and architecture. Refreshing every 30 minutes is common, and I have seen intervals of 5 minutes.  Frequent refresh of large volumes usually requires incremental load, which is covered in the Reference Guide and the Forums.
Q: ­If add the BUFFER command before each load statement pulling from DBMS, the first execution pulls from the DB but all after are incremental loads pulling from a file system created batch of QVDs?­
The BUFFER prefix does not provide incremental load when loading from a DBMS.  Subsequent reloads will load from the buffered file system QVD, but new rows will not be fetched from the database.
When used with a load from txt files, BUFFER will provide automatic incremental load. Subsequent reloads will add new data from the file to the buffered QVD.
Q: I am running SBE Server so documents are reloaded right from the Documents folder.  What is your recommendation for location of the QVD generator documents?  In other words, do you place them in the Documents folder alongside your production QVW’s? 
I recommend putting the QVD generators in a separate “Loaders” folder. Make this a mounted folder in QVS and schedule reloads as needed. Use NTFS permissions to hide the folder from standard AP users
A number of questions were asked about the QVX format. I haven’t had much experience with QVX yet. Rob Patterson has indicated he will schedule a QlikLearn webinar specifically on the topic of QVX.
Q: Does ­QVX also have two types of load, optimized and not optimized?­
No optimized load only applies to QVD.
Q: ­What are the other differences between QVD and QVX?­
QVD is a proprietary file format provided by QlikView for storage. Only QlikView software can read and write to QVD files.
The QVX is in an open format performance file for storage of QlikView data. A customer or third party can create QVX files on any platform, without needing Qlikview software.
QVD files will typically load faster than a QVX file.
Q: ­Is QVX used as a source to other source systems or is it used to pull the data from source systems which has no ODBC provider?­
The use cases for QVX are still being discovered, and I’m sure we’ll see some interesting uses.  The scenario I currently understand is to provide data to Qlikview when there is no ODBC provider.
Q: ­How can I create a QVX?
Q: ­How do you write out to a QVX?­
Q ­How can you read QVX from other software than Qlikview .­
Documentation of the internal QVX format is available in the Qlikview SDK. The SDK can be installed from the Qlikview Server installation package. Also look for examples in the “Share Qlikviews” section of QlikCommunity.
You can also create a QVX with a script STORE statement:
               STORE mytable INTO mytable.qvx (qvx);
This is useful to generate a sample QVX for examination or testing.

Incremental Load using SQL Server “timestamp” Data Type

Incremental Load (extracting only new or changed rows from the database) requires a table column that identifies when a row has been updated. This is usually a datetime column like “LastUpdate”. The script extracts the max timestamp from the existing QVD and uses it to create a select predicate like:

WHERE LastUpdate >= ’01-20-2010 13:55:01′

If the database is Microsoft SQL Server, you may have another option for identifying updated rows. A table column of type “timestamp” is incremented whenever a row is changed. The name “timestamp” in somewhat confusing, as this column does not contain time values. Here is the description from the 2005 Reference:

“timestamp is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.”

In Sql  Server  2008, the type “rowversion” was introduced as an alias for “timestamp”. Rowversion is preferred for 2008 and forward.

If your table does not already have a timestamp column, you can add one with an ALTER DDL statement, For example, to add a timestamp column named “Revision” to a table named “Orders”:

alter table Orders ADD [Revision] [timestamp] NOT NULL;

The Revision column will be returned by default as a hexadecimal string, so it’s easiest to convert to an integer before storing in QV. 

    OrderID, etc, 
    cast(Revision as bigint) as Revision
FROM Orders …

In subsequent incremental loads, add a predicate to select only rows greater than the last revision. 

// Select the max value from the qvd
LOAD max(Revision) as Revision FROM myqvd.qvd (qvd);
LET vMaxRevision = PEEK(‘Revision’);
DROP TABLE tempmax:

  OrderID, etc,
  cast(Revision as bigint) as Revision
FROM Orders
WHERE Revision > $(vMaxRevision);

I find the timestamp value, when available, to be easier to use than a datetime column. It’s just a numeric, so no literal formatting is required. 

Because it’s a precise and unique value, you avoid the “always one row” problem. When selecting from a datetime, you usually have to specify “>=” because a datetime is not a unique value. This means that a select will return at least one row, even if there were no real updates. 

With timestamp, you can specfy “>” which will return zero rows if there were no updates. Knowing that you had no updates can be useful in controlling the execution of further processing steps. 



Deconstructing Chart Design

In Qlikview training we survey chart types and visualization principles. In this post I’ll walk through a recent Qlikview assignment and how I applied those design principles.

The problem statement is this. System A sends many thousand transactions daily to System B and requires a reasonable response time to support the business process involved. Occasionally, the response times are atypically high and this causes problems in the downstream processes. The high response times do not always appear in clumps. Engineers require identification of the specific problem periods and feedback on the success of their tuning efforts.

Qlikview’s aggregation and drill down capabilities seem ideally suited for this problem. What metric will support the monitoring and analysis goal?

Due to the large transaction count, looking at average response time is ineffective. Two hundred 50 second responses averaged with 10,000 1-3 second transactions will not stand out enough in an average. The customer also stated that having several outliers of 200 or 300 seconds were normal and could be explained by workloads or scheduled maintenance on System B.

While there may be other statistical measures that would work, we determined that tracking the 99% percentile would identify the problem periods. Other supporting measures will be displayed to provide context for the primary measure.

The primary measure is 99% Percentile (99% of the transactions response fall at or below this value). Supporting measures will be Transaction count, Average Response Time and Max Response Time.

When I approach a new chart I try to begin with Edward Tufte’s advice to  “always know what story you are trying to tell before you design the graphic”. This story is the trend of the 99% Percentile.

Here’s my first attempt at a chart using mostly defaults. When plotting values of different magnitudes (Transaction Count and Transaction Responses) I like to use the Combo chart type.

Automatic scaling causes the red “Avg Response” line to have no meaningful shape. Further, the Transaction bars have too much prominence. They are supposed to be a supporting measure to the main story.

I change the Response values to a Logarithmic scale (Properties, Axes, Log Scale) and de-emphasize the Transaction bars by making them white (Properties, Color) and with borders (Properties, Expressions, Bar Border Width=1), Here’s the result.

The Response line now has meaningful shape, but the Transaction bars are still too prominent.

For my next revision I split the axis vertically 75/25 (Properties, Axes, Split Axes, Primary=75%). This allows the top 75% of the chart space to represent the Response lines. The remaining 25% of the chart will host the Transaction bars. I change the bar colors to a muted gray (Properties, Colors) and remove the borders.  Here’s the result.

The only prominence granted to the 99% is it’s being first in the legend, which is not enough.

For my final revision I apply Stephen Few’s principle of color and hue to emphasize the key measure. I assign the 99% Response the red color to make it stand out. I de-emphasize the supporting measures by removing the lines and using only symbols (Properties, Expression, Display Options, uncheck Line, check Symbol). I leave Symbol on for 99% to make it easier for the user to get the hover popup and drill down.

Guided by Tufte’s “Data-Ink Ratio” principle, I remove axis numbers for the Transaction bars (Properties, Axes, Hide Axis) to make the chart less busy. The significance of Transactions is trend and it’s relationship to 99%, not the absolute value. Driven by the same principle, I remove the grid lines as they are unnecessary. The story and the objective is to identify spikes, not absolute values. Here’s my final chart.


Alert Questions & Answers

I received several questions regarding my post Monitoring the Reload Schedule. Surprisingly the questions were not about the monitoring solution, but rather using Alerts. I’ll summarize the questions and answers here.

Can I loop through field values in an Alert?

Not directly. But you can create multiple alerts that use either bookmarks or set analysis in the Condition to handle a known set of field values. For example create one alert for Region=US and a second for Region=Europe and each sends an email to the associated Regional Manager.

Can I use an alert to always send an email?

Yes. Just set the Condition to “-1” (without quotes). This is always true and can be used to send text “mini-reports” to your email recipients. Your email text might be something simple like:

=’Currently open tickets: ‘ & sum(OpenTicketCounter)

Or something more complex like:
=’YTD Sales are ‘
& money(
<[Invoice Year]={$(=year(today(2)))}>
Quantity * Price
, ‘$#,##0;($#,##0)’
& ‘
YTD Orders are ‘
& num(
<[Order Year]={$(=year(today(2)))}>
, ‘#,##0’

This will generate an email body that looks like:

YTD Sales are $12,014,788
YTD Orders are 167,580

You can build up complex expressions in a Text Object to get them correct and then paste to the Alert.

Can I include a chart image in an alert email?

No. If you know of a method, please leave a comment.

It may be helpful to include a url in the email that opens the Qlikview document for further analysis.

If you have Alert questions or tips, please leave a comment.



Monitoring the Reload Schedule

Monitoring successful operation is an important aspect of any IT system.  What kind of monitoring is useful for Qlikview? I’ve found the following monitoring important in a Qlikview Server installation:

  • Notification of individual reload failures. This capability, sending an email on reload failure,  is included in all editions of Qlikview Server.
  • Status of Qlikview services. Most shops have a network monitor such as Servers Alive to monitor and send alerts about status of Windows services.
  • A particularly effective style of monitoring is “goal” monitoring. That is, instead of monitoring the resources required to achieve a goal, monitor the goal itself. In Qlikview terms, this means confirming that a set of Qlikview documents has been updated as scheduled.

In this post I’ll look at using a Qlikview document to monitor the filetimes of Qlikview Server documents. An “old” filetime is an indication that a reload or distribution has been missed. An email notification will be sent when we are off schedule.The mail body will look something like this:

4 documents overdue: Expenses_Joe.qvw, Expenses_Rob.qvw, Expenses_Sally.qvw, fieldIndex.qvw

The code used in this post can be downloaded at File Age Monitor. Download and extract the three files:
FileAgeMonitor.qvw  — monitoring document.
FileAgeMonitor_Rules.txt — Filename masks and maximum expected age.
FileAgeMonitor_Email.txt — Address(s) to send alert about overdue documents.

To use FileAgeMonitor in your shop you’ll need to make the following changes:

  1. On the “Configuration” script tab, specify the directories you want to scan for qvw files.
  2. Modify FileAgeMonitor_Rules.txt to specify rules meaningful to your installation, Instructions are in the file.
  3. Modify FileAgeMonitor_Email.txt for your email address. Instructions are in the file.

FileAgeMonitor_Rules.txt consists of two fields:
– a filename mask (Key)
– a maximum allowable age in hours (MaxAge).
Lines beginning with “#” are comments. Example:

Key, MaxAge
# Rules file for FileAgeMonitor.qvw. First match wins.
# “Filename Mask”, “Max allowable age in hours”
# All of the Expenses* docs should be no more than 25 hours old
Expenses*.qvw, 25

#Films can be 90 days old
Films.qvw, 90*24

# Everything else – catch all default – 7 days
*, 7*24

The first field, “Key” is a filename mask that may use the standard Qlikview wild card characters of “*” to match any number of characters and “?” to match a single character.

The second field, “MaxAge”, is the threshold age at which a file is considered “overdue”. MaxAge may contain any expression that evaluates to a numeric value. The value is hours.

The last entry in the Rules files, “*” will match all files.

The script builds a list of qvw files and matches each filename against the entries in the Rules file. The first match wins.  The age of each file is tested against it’s matching rule and the flag field “Is Overdue?” is set to Y or N. The flag field is defined as a dual:
  if(FileAge > MaxAge, dual(‘Y’,1), dual(‘N’,0) )
as “Is Overdue?”.

Y has the dual value 1, N the value 0. This allows the flag to be summed.

So now we have a chart that displays what’s overdue, but how about automatic notifications? For that we’ll use a Document Alert. Alerts are created from the Tools menu. Here’s the alert defined used to send the email. Refer to the notes that follow the picture.

. Before the condition is evaluated, apply the bookmark that selects “Is Overdue?”=Y.
2. The alert condition is specified as:
=sum([Is Overdue?])  > 0
Recall that “Is Overdue?” was defined as a dual so it may be summed. If there are any overdue documents, the condition will be true and the alert will “fire”.
3. Both the Mail subject and mail body contain the count of overdue documents. The body contains the document names as well. The body expression is:
=sum([Is Overdue?]) & ‘ documents overdue: ‘ & concat(FileName, ‘, ‘)
4. The email will be sent to the addresses that were loaded into field “AlertTo”.  The script loaded this field from the file FileAgeMonitor_Email.txt.
5. Batch Mode limits this alert to server based reload only. If Interactive were checked, the alert may fire when we are reloading during Development.
6. The Alert will be tested at the end of each reload.
7. The trigger level is set to “Message Changes”. This means a new email will be sent only when the count of overdue documents changes. So we will not get an hourly email telling us that “10 docs are overdue”, but will receive a new email if the next reload produces an overdue count of 8 or 12 — something different than 10.

FileAgeMonitor itself needs to be scheduled to reload periodically. I schedule it to run towards the end of interval reload cycles. For example, if there are hourly reloads at the top of the hour, I schedule FileAgeMonitor at 45 minutes after the hour.

If FileAgeMonitor relies on Server scheduling, and the entire scheduling process fails, how will FileAgeMonitor be able to tell us that reloads are not running? This is the “monitoring the monitor” problem that inevitably occurs with system monitoring. I address this issue by monitoring the Filetime of FileAgeMonitor.qvw using an external monitor like Servers Alive.

Qlikview reloading can get “off schedule” for any number of reasons; Database errors, Administrator errors. bugs in the scheduling software.  It’s important for the Administrator to know of exceptions and their scope as quickly as possible.