Qlikview Notes

Incremental Load using Qlikview Components

2012-01-18T22:01:00.000-08:00

Qlikview Components (Qvc) is a script library that simplifies and improves the quality of Qlikview scripting. You can read an introduction to Qvc here.

Today I'll show you how to do Incremental Load (IL) using Qvc. Incremental Load means extracting only the latest changes from a database table and merging those changes with a master QVD file. Writing your own IL script can be rather tedious and frequently involves copy/paste operations followed by forgetful edit errors. Qvc can make it much easier.

Incremental Load is not necessary for every table. We typically use IL for large source tables. IL can reduce the elapsed reload time and impact on the database system by loading only new or updated rows.

To utilize Incremental Load a source table must have both of the following attributes:

A unique identifier -- a Primary Key -- for each row.
A "Modification" column that identifies when a row was added or updated. The column type may be a Date, Datetime or ascending Revision number.

The classic IL logic is this:

Determine the "Last reload Time". The most robust technique is to extract the max value for the "Modification" column from the Master QVD.
Select rows from the database table where "Modification" is greater than "Last Reload Time".
Add and update rows in the Master QVD, based on primary key.

Here is the complete Incremental Load script using Qvc.

// Include Qvc code
$(Include=..\qvc_runtime\qvc.qvs)

// Calling parameters are QVTablename, UpdateColumn, PrimaryKey.
CALL Qvc.IncrementalSetup ('Rates', 'LastUpdate', 'RateId');

// v.Tablename is set by IncrementalSetup
[$(Qvc.Loader.v.Tablename)]:
// Whatever LOAD and SELECT goes here
SQL SELECT * FROM dbo.RateTable
// v.IncrementalExpression is set by IncrementalSetup
WHERE $(Qvc.Loader.v.IncrementalExpression) ;

// Update the QVD with the changed rows
CALL Qvc.IncrementalStore

That's it.

You'll also get useful log messages telling you what was done.


00002 1/17/2012 1:23:46 PM; QVD\Rates.qvd exists, rows=31
00003 1/17/2012 1:23:46 PM; Loading rows where LastUpdate >=''01/14/2011''
00004 1/17/2012 1:23:46 PM; Rates loaded, rows=1
00005 1/17/2012 1:23:46 PM; QVD\Rates.qvd updated, rows=31

Review the Qvc Documentation for explanation of parameters and configuration variables. The documentation also contains a working example.

The Power of Gray

2012-01-09T11:10:00.000-08:00

I'm sometimes asked by developers "How do I make un-associated (gray) data disappear from listboxes?". After some discussion as to why they want to do this, I usually attempt to convince them to not hide any data.

Gray data is an important part of the power of Qlikview -- the associative experience. Associative is the way our mind connects data. When I try to recall a face I may narrow my candidates by an attribute like hair color or where we met. But I don't forget everyone else. I just take them out of focus.

I recall one of my first Qlikview "Wow!" moments when I saw the power of the associative process. I was working with a Industrial Safety analyst who was studying the effectiveness of various glove styles in preventing hand injuries. We had a database of injuries and a separate dataset of glove models in use by period and location.

Up to this point, the analyst had been working from static reports that counted injuries of interest by looking for "where BodyPart='Hand'".

We loaded the data into Qlikview. BodyPart became a listbox. He selected "Hand" and the table charts displayed the same counts as the static reports. In the gray data we noticed a significant number of injuries for "Hend", a misspelling of "Hand". A simple click added those into the total. The analyst also discovered "finger" and "fingers" were of interest and added those. Furthermore, for certain glove styles -- those with a gauntlet sleeve -- "forearm" should be added.

With just a few clicks the Analyst had greater accuracy in this important work. It was the display of gray data that allowed this to happen, along with the ability to quickly convert gray to green (selected).

So while there may be edge cases where it's necessary to hide gray data, I generally find that displaying all data in the associative manner is a unique and special advantage of Qlikview.

Easy Period Analysis using Qlikview Components

2012-01-05T18:07:00.000-08:00

If you think coding complex Set Analysis expressions and writing repetitive script is an efficient use of your QV development time, then this post is not for you. Go back to memorizing pi or whatever it was you were working on. For the rest of us, read on.

With Qlikview Components (Qvc) you can quickly build all the parts needed for many point in time and period over period analyses. Let's walk through an example.

Download the latest distribution (this article require V0.7+) of Qvc from the download link at

http://qlikviewcomponents.org.

Unzip the distribution file anywhere on your disk. If you are new to Qvc, start by reading the ReadMe file.

Let's assume our QVW contains a fact table of Orders with a field named "OrderDate" that we will use as the date for period reporting.

Add this include at the beginning of the script to bring in the Qvc routines.

$(Include=..\qvc_runtime\qvc.qvs);

After loading the fact table, generate a Master Calendar with this call:

CALL Qvc.CalendarFromField('OrderDate');

The Qvc.CalendarFromField routine will build a calendar with dimension fields named OrderDate, Day, Month, Year, Year-Month and Quarter. You can optionally include a language file to use your local language for the fieldnames -- in this example Swedish.

$(Include=..\qvc_runtime\language\qvc_language_SE.qvs);

Now that we have a calendar, we can use any of the calendar fields in listboxes or chart dimensions. And... Qvc.CalendarFromField() also creates a collection of set analysis expressions stored in variables. The variables follow the naming convention vSetPeriod. For example:

vSetYTD

vSetPreviousYearYTD

The documentation for Qvc.Calendar provides a list of all generated vSet* variables.

These variables in a chart expression like:

=Sum(Sum($(vSetPreviousYearYTD) OrderQuantity)

which will sum up OrderQuantity for YTD of the previous year. The actual set analysis expression is rather lengthy but the variable is compact.

I'd like to credit Mike over at iQlik for first teaching me the set analysis variable idea at http://iqlik.wordpress.com/2011/01/01/point-in-time-reporting-out-of-the-box/

Now we can quickly whip up a chart like this without coding any of the complex stuff:

The documentation supplied with Qvc provides examples for all Qvc routines. On the Documentation.qvw "Details" sheet select Qvc.CalendarFromField. You'll be able to open a sample qvw where you can explore the vSet variables and learn how they interact with selections.

If you have comments or questions about Qvc, use the User Forum found on the project page.

Introducing Qlikview Components

2012-01-02T17:39:00.000-08:00

Qlikview Components is a set of Qlikview scripting subroutines. I started this project to address two problems.

As a Consultant, I've written and left behind QV script for many different customers. I frequently use script subroutines for common tasks to simplify implementation and maintenance. As Qlikview (and me) grows and matures, better scripting approaches and product features become available. What's missing is an effective way to feed those improvements back in to the various versions of code I've spawned in the world.

Another problem is that the flexibility of scripting is also a curse. There are many ways to do the same thing. What's the "best" way? Do we all need to learn that variable loop and load thing? Many script tasks are like plumbing in a building. They are not that interesting (my apologies to Plumbers) but it is essential that they work correctly. We want to get them done quickly and move on to the interesting bits.

How to address these problems? Enter "Qlikview Components" (Qvc), an open source (free) project. Here's the mission statement from the project home page:

The mission of Qlikview Components is:

Implement scripting best practices encapsulated by a simple and stable API.
Improve the speed and quality of script development.
Create common ground between script developers.

QVC aims to serve the needs of the "big middle" of script tasks. Not the simple tasks, not the very complex tasks, but the garden variety common needs found in many scripts.

As an open source (OS) project, we have the opportunity to capture and redistribute the broad knowledge of the Qlikview Community. What do I see as the strengths of this project?

The broadest possible input for design, validation and improvement of code. The project is hosted on google-code and allows for multiple developers around the world to contribute to the project.
A stable API. We're committed to continuous improvement and compatibility.
A unit testing regime that ensures quality.
All the supporting pieces one would expect in a serious project -- documentation, examples, localization, issue tracking, discussion groups, formal testing and build systems.

Qlikview Components is currently in a "Preview" stage with a target for first production release on Feb 1, 2012. You can find the project at

http://QlikviewComponents.org

That address will redirect to the hosting site on google-code where you'll find downloads and links to discussions.

I'll be blogging more on Qvc in the coming weeks. In the meantime, here's a taste of what it takes to build a typical master calendar using Qvc.

$(Include=..\qvc_runtime\qvc.qvs);

// Load fact tables, etc.....

CALL Qvc.CalendarFromField('ClosingDate');

I hope you'll find Qvc useful and look forward to hearing your ideas for improvement in the discussion groups!

Initial Selections and Current Selections

2011-12-28T20:08:00.000-08:00

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:

=Year=year(today(1))
=Month=month(today(1))

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.

-Rob

Correct Time Arithmetic

2011-10-05T20:21:00.000-07:00

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#('09','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:

$(AddTime(mytime,-MakeTime(1)))

-Rob

The Case Against Caption Icons

2011-06-05T16:59:00.000-07:00

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+.

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".
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.
They force the designer to display a Caption even when a Caption is not desirable for the layout.
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."?
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.

-Rob

My Top Two Feature Requests

2011-05-11T18:22:00.000-07:00

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?

New Metadata in QV10

2011-05-02T15:29:00.000-07:00

Qliktech is rolling out some exciting new metadata in Version 10 SR2.I've started poking around at the new data along with a new metadata scanner application that should be available to the Community next week.

The most intriguing new feature is that certain information, such as source tables used in load statements, will now be available as XML data within each QVW. Object definitions, expressions and dimensions will also be available through the same XML mechanism.

Qliktech will be providing a scanner application that will read the new XML data. In addition, the scanner will load information on existing QVDs, Server and Publisher logs and server tasks. The loaded tables form a single metadata model that can be used to feed reporting and analysis such as Ops Monitor.

I'm excited about these changes on two fronts:

1. The "missing" metadata -- data lineage -- is now starting to appear in the product. Up to this point, customers and consultants have created various schemes to track data lineage.

2. I see the formal Qliktech metadata model, begun in V9, maturing to the point where it will become accepted as the standard.

I'm looking forward to creating applications with the new data and will blog again as I learn more.

Document Compression

2011-03-28T10:03:00.000-07:00

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?

Zero.

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 will usually 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	Destination	Ratio	Example Result	Notes
Source DB	-		2GB	Raw Data
Source DB	Document RAM	10:1	200MB	Data de-duplication
Document RAM	QVW Disk	3:1	67MB	Save Compression=High
Document RAM	QVW Disk	1:1	200MB	Save Compression=None
QVW Disk	Document RAM	1:3	200MB	Save Compression=High
QVW Disk	Document RAM	1:1	200MB	Save Compression=None
Document RAM	QVD Disk	1:1	200MB	QVD always uncompressed
QVD Disk	Document RAM	1:1	200MB	Optimized load
QVD Disk	Document RAM	1:2	400MB	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

2011-02-16T22:06:00.000-08:00

On Feb 2 I presented a live webinar titled "Understanding & Best Practices with QVD Files". You can view a full 36 minute recording of the presentation or 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?

There is nothing inherent in a QVD that would provide source lineage.

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”).

MyMap:

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.

MyMap:

MAPPING LOAD F1, F2 FROM sometable.qvd (qvd)

WHERE 1=1;

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);

(Note: In QV10, MAPPING LOAD appears to trigger an un-optimized load, rendering the WHERE 1=1 unnecessary. I have not finished verifying this).

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 appears to trigger an un-optimized load, rendering the WHERE 1=1 unnecessary. I have not finished verifying this).

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);

NEXT i

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.

-Rob

http://robwunderlich.com/

Incremental Load using SQL Server "timestamp" Data Type

2011-01-14T16:39:00.000-08:00

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.

SQL SELECT
   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
tempmax:
LOAD max(Revision) as Revision FROM myqvd.qvd (qvd);
LET vMaxRevision = PEEK('Revision');
DROP TABLE tempmax:

SQL SELECT
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.

-Rob

Deconstructing Chart Design

2010-12-09T11:30:00.000-08:00

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

2010-12-06T22:35:00.000-08:00

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(
sum({1
<[Invoice Year]={$(=year(today(2)))}>
}
Quantity * Price
)
, '$#,##0;($#,##0)'
)
& '
YTD Orders are '
& num(
sum({1
<[Order Year]={$(=year(today(2)))}>
}
Quantity
)
, '#,##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.

-Rob

Monitoring the Reload Schedule

2010-09-14T17:16:00.000-07:00

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:

On the "Configuration" script tab, specify the directories you want to scan for qvw files.
Modify FileAgeMonitor_Rules.txt to specify rules meaningful to your installation, Instructions are in the file.
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.

1. 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.

-Rob

The Now() Trap

2010-07-08T09:18:00.000-07:00

The Now() function operates differently in script and charts. This post will highlight one difference that has tripped up even experienced developers.

Our example script requirement is to extract rows of data where the "TransactionDatetime" field value is within the last 24 hours. We'll use the "Now()" function to establish the current time. Here's the doc from the QV Ref Guide.

now( [ timer_mode ] )

Returns a timestamp from the system clock. The timer_mode may
have the following values:
0 Time at script run
1 Time at function call
2 Time when the document was opened

Default timer_mode is 1. The timer_mode=1 should be used with
caution, since it polls the operating system every second and hence
could slow down the system.

Which timer_mode option do you choose? Hmm... you want 24 hours prior to the script execution so it seems like "0" is the best option. Plus there is that scary warning about "1" slowing down the system.

The integer 1 represents a day -- 24 hours -- so you code a where clause in your LOAD like:

WHERE TransactionDatetime >= now(0) -1

During development, you reload several times. You examine the data selected and it seems to be working -- you are getting data from the last 24 hours. It's 4:00pm Tuesday afternoon and you promote this to the server and set a reload schedule of daily at 8:00am. A test run on the server shows the data selection is working as planned.

The document reloads on schedule at Wednesday 8:00am. A review of the app shows data going back to Monday at 4:00pm. After Thursday morning's reload the data range is Tuesday 8:00am to Wednesday 8:00am. What happened to the rest of Wednesday and all of Thursday?

The value for Now(0) is set at the end of script execution. When used in script, Now(0) returns the end time of the previous script execution, not the time of the current script execution.

So on Thursday morning Now(0) returns Wednesday 8:00am -- the end of the last execution. That is not what we were looking for.

In Charts, Now(0) returns the end of the latest script execution, which would be Thursday 8:00am.

Now(1) always returns the time when the function is executed -- the "current" time. During development when reloads are frequent, script Now(0) is usually pretty close to Now(1) and you may not notice the difference. But when a document goes on a daily reload schedule, Now(0) is usually an entire day behind what you want!

The correct choice in script is generally Now(1), which returns the time when the function is actually executed, not a time related to previous reload.

To Summarize, use Now(1) in script, Now(0) in charts.

If you need to establish a deterministic (consistent throughout the entire script) value for "Now" in script, set a variable at the beginning:

LET vScriptStart - Now(1);

Then use vScriptStart as the reference point in your script. It may also be used in charts as well.

-Rob

Use cases for Generic Load

2010-05-21T17:24:00.000-07:00

The Qlikview "Generic Load" is not frequently used and is therefore sometimes forgotten. Generic Load has some interesting applications and can be a useful item in your script toolbag.

Generic Load is the complement of "Crosstable Load". In a loose sense, you could say that a Crosstable Load creates a Generic table and Generic Load creates a Cross table.

Consider this table which contains a separate row for each Phase of a Project.

Now suppose you want to flatten this table to a single row per Project. You have a variable number of Phases per Project. The resulting data model should look like this:

The model above can be accomplished with a two GENERIC LOADs against the ProjectTable:

GENERIC LOAD Project, 'Start Phase ' & Phase, StartDate
RESIDENT ProjectTable;

GENERIC LOAD Project, 'End Phase ' & Phase, EndDate
RESIDENT ProjectTable;

Generic Load creates additional Qlikview tables. The additional tables cannot be avoided by combining a CONCATENATE or JOIN prefix. In the next example I'll offer a technique to consolidate the tables.

Here's another application of Generic. Consider this example table.

Suppose you want to generate flag fields for each of the possible order statuses? The flags could be created with a single Generic Load.

Flags:
GENERIC LOAD Order, 'Status_' & Status, 1
RESIDENT OrdersTable;

The resulting data model now contains flags for each Order.

As mentioned previously, Generic Load creates additional tables. The table view after the above Generic Load is:

You can see Generic makes a new table for each new field it creates. That's fine if it doesn't cause synthetic keys or other problems. If you want to merge the Flag fields into the fact table (OrdersTable), you can do it after the Generic Load with a bit of code like this:

FOR i = 0 to NoOfTables()

TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'Flags.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (OrdersTable) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i

DROP TABLE TableList;

Here's the table view after the Joins.

Generic load is not an everyday tool, but can prove useful in specific situations.

The qvw examples used in this post may be downloaded from here.

-Rob Wunderlich

Reusing expressions -- chart column references

2010-02-20T11:42:00.000-08:00

It's often useful to avoid repeating lengthy expressions. Here's one technique for reusing expressions.

Consider the chart below. The Expression for the column labeled "Net" is:

=sum(Quantity * Price) * (1-Discount)

Let's add another column for "Commission", defined as 10% of Net. Instead of repeating the Net calculation, use the expression:

=Net * .1

Qlikview allows referencing the value of another column in the same chart by name. The name is the value assigned to the column label. "Net" in this case is not a Field. It is a symbolic reference to an expression. It may be used in other expressions in the same chart, but not in places where a Field name is expected.

=Net / 2          Allowed
=above(Net)   Allowed
=Sum(Net)        Not allowed

Column references can be very useful in Expression attributes. Let's bold any Commission value over $200. We'll use a Text Format expression of:
    =if(Commission > 200, '<B>')

giving this result:

-Rob

Setting Initial Selections in Version 9

2010-02-08T10:05:00.000-08:00

A common Qlikview requirement is that when a user opens a document, some type of dynamic selection -- like current date -- should be made.

In QV 8 this entailed assigning a macro to the Document OnOpen event and using macro statements similar to this:

ActiveDocument.GetField("Year").Select ActiveDocument.Evaluate("year(today(1))")

The Actions feature of QV 9 has eliminated the need to use macros for initial selections. In V9, one or more Actions can be assigned to the Document OnOpen trigger. The actions may be "Select In Field" using a expression like =year(today(1)).

A very modular way to make Initial Selections in V9 is to make all the selections you require using search expressions and then save those as a Bookmark. The Bookmark can then be applied as the OnOpen Action. For example, here are selections reflecting current Year, Month and Weekday using search expressions.

Example qvws that demonstrate initial selections in both V8 and V9 can be downloaded here http://robwunderlich.com/downloads

-Rob

Best way to count keys? Don't.

2010-01-12T15:54:00.000-08:00

I was recently reviewing a problem where a chart Count() function produced differing results between QV 8.5 and 9. The field being counted was a key field. Counting a key field without using DISTINCT, especially a one-to-many key, can produce ambiguous results and should be avoided.

The recommended approach is add a counter field and sum() that field. For example, in an Orders table, add the field:

1 as OrderCounter

and then count Orders with:

sum(OrderCounter)

Yes, count(OrderCounter) will work as well. The Qlikview literature still states that sum() is faster and preferable to count(). John Witherspoon recently showed me some tests that demonstrate count() being faster than sum() in Version 9, so it's possible that recommendation should be examined if you are working on a very large application.

-Rob

Simplify with Preceding Load

2009-12-04T16:21:00.000-08:00

Most QV script developers are introduced to "preceding load" as a LOAD that precedes an SQL SELECT. But a LOAD may also precede another LOAD, which can be a very useful tool.

Let's review a typical preceding load.

Table1:
LOAD Customer, Sales, today(1) as LoadDate ;
SQL SELECT Customer, Country, Sales FROM SalesResults ;

The absence of a "FROM" or "RESIDENT" clause in the LOAD is what makes this a "preceding load".
The SQL SELECT will be executed first. The results of the SELECT will be used as input to the LOAD statement.
Table1 will have three fields -- Customer, Sale, LoadDate.
The Field "Country" will not be present in Table1 because "Country" is not repeated on the LOAD statement.
The field "LoadDate" does not exist in the SQL SELECT and is added by the LOAD.

Let's look at an example of where preceding load can be useful. When loading data, you may need to use expressions to parse or cleanse data. For example, extracting a timestamp from a string in a text file.

Table1:
LOAD
timestamp(timestamp#(mid(@1:n,3,12), 'MMDDYYhhmmss')) as EventTime,
mid(@1:n, 17) as Event
FROM myfile.txt (fix, codepage is 1252);

What if you want additional time dimensions from the data? You could add expressions like:

date(date#(mid(@1:n, 3, 6)) as EventDate
month(date(date#(mid(@1:n, 3, 6))) as EventMonth

The script would soon get messy with "paren-disease" and become harder to maintain. Preceding Load to the rescue.

Table2:
LOAD *,
floor(EventTime) as EventDate,
month(EventTime) as EventMonth,
year(EventTime) as EventYear,
hour(EventTime) as EventHour
;
LOAD
timestamp(timestamp#(mid(@1:n,3,12), 'MMDDYYhhmmss')) as EventTime, mid(@1:n, 17) as Event
FROM myfile.txt (fix, codepage is 1252);

The syntax is greatly simplified by reusing the "parsed once" EventTime.
Table2 will contain six fields: Event, EventTime, EventDate, EventMonth, EventYear, EventHour.
The "*" in the top load includes the fields emitted by the bottom load -- EventTime & EventTime.

Preceding Loads may also be stacked more than two deep as in this example.

Table2:
LOAD *,
if(match(EventMonth, 'Aug', 'Dec') OR weekday(EventDate) > 5, 'Holiday', 'Standard') as Rate;

LOAD *,date(floor(EventTime)) as EventDate,
month(EventTime) as EventMonth,
year(EventTime) as EventYear,
hour(EventTime) as EventHour;

LOAD timestamp(timestamp#(mid(@1:n,3,12), 'MMDDYYhhmmss')) as EventTime,
mid(@1:n, 17) as Event
FROM myfile.txt (fix, codepage is 1252);

Preceding load is a useful tool to simplify the syntax of your script and make it easier to maintain.

-Rob

Understanding Join and Concatenate

2009-11-19T15:39:00.000-08:00

The Qlikview script functions JOIN and CONCATENATE can sometimes be used to tackle the same problem, but there are important differences that should be understood.

Examine the sample tables below. Note that they share one common field name, "Key". Also note that Table1 has a Key value "3" that is not present in Table2.

JOIN will combine rows where the Key value matches. The keyword OUTER will also retain rows that do not match rows in the other table. Here's what the merged table will look like after an outer join.

OUTER JOIN (Table1) LOAD * RESIDENT Table2;

Values A1 and C1, which were in different tables, now occupy the same row in the result table. The row with Key 3 has missing values for C & D, because there was no matching Key in Table2.

Creating a chart that uses "Key" for dimension will produce results similar to the Table Box above.

The important point is that values with the same Key value have been merged together into a single row. If value A1 is selected, note that values C1 & D1 remain associated (white). The set A1,B1,C1,D1 is indivisible.

Now let's look at Concatenate. Concatenate appends the rows of one table to another. Concatenate never merges any rows. The number of rows in a concatenated table is always the sum of the rows from the two input tables. Here's what our sample data will look like after Concatenate.

CONCATENATE (Table1) LOAD * RESIDENT Table2;

Rows with like Key values are not merged together. The rows from Table2 are simply appended to Table1. Because the tables have different fields, rows will have null values for the fields from the "other" table.

If the data is used to build a chart that utilizes the common field "Key" as dimension, the chart looks just like the JOINed table.

Let's make the selection "A1" in Field A and see it's impact on our visible charts and tables.

When A1 is selected, the association to C1 & D1 is lost and C/D values become null in both the Chart and Tablebox. We cannot select both A1 and C1. This is a different result than the JOINed example.

Let's consider a more realistic example where we may choose between JOIN and CONCATENATE. Consider the two tables below. Note that only one BudgetAmount row is present for each Region-Year combination. In the Sales table, the SalesAmount is broken down by Department within Region.

If we load both tables we can produce a chart using expressions like =Sum(BudgetAmount).

The Budget and Sales values have been summed correctly.

We then notice that we have an undesirable synthetic key, created by the Budget and Sales tables sharing the Year and Region fields. One approach to eliminate the synthetic key would be JOIN or CONCATENATE. But which one in this case?

Let's try JOIN and see what the Chart looks like.

OUTER JOIN (Budget) LOAD * RESIDENT SALES

The summed Budget numbers are incorrect!

A look at raw data of the joined table will identify the problem. The JOIN repeated the BudgetAmount value on each Department row.

Let's try with CONCATENATE.

CONCATENATE (Budget) LOAD * RESIDENT Sales;

The numbers are now correct and we've accomplished the goal of eliminating the synthetic key.

A peek at the data in the Concatenated table will make it clear why the chart is now correct. There is only BudgetAmount value or each Year-Region.

JOIN and CONCATENATE are both very useful and frequently used functions in Qlikview. It's important to understand the differences between them.

-Rob

Customer Portal Delivers

2009-11-15T11:02:00.000-08:00

I've blogged in the past and buttonholed Qliktech employees about the need for greater visibility of bug tracking and enhancement requests. The new Customer Portal delivers just what I was looking for and more.

The Customer Portal is available from a tab on the www.qliktech.com or QlikCommunity web sites. The Portal uses your existing Qlikview userid (SSO) and provides a link to register if you are new.

In addition to online support case submission and tracking, the Portal offers:

Searchable database of issues and solutions reported by other customers.
Submission of "ideas" (enhancement requests). You can also vote and comment on ideas.
Technical papers and how-to videos.
License inventory for your site.

I've found the Portal to be easy to navigate and the response good. It's so easy I don't have any tips to offer. I like the PDF viewing feature in the Content section and wish we could do the same for videos.

I encountered one small glitch in my initial login to the Portal. I was already logged in to QlikCommunity and entered the Portal from the tab. The Portal declared that my password was expired and required me to change it. The result was that my QlikCommunity and Customer Portal passwords were no longer synchronized. I rectified the problem by changing my password in QlikComuunity to match.

Content is still being moved in from the old locations. It may be a few more days before all the content is there. As is frequently the case in migrations, some text formatting and history is lost when content is moved from old system to the new. For example, most of the "ideas" look like they were submitted by a single user. I'm guessing this is the user assigned to copy the entries over from the existing "Enhancement Requests" database.

All in all, an exceptional resource for Customers. Thanks to the Qliktech team for getting this in place.

BOQC: Flexible Interval Classification

2009-05-12T23:13:00.000-07:00

Another post in the "Best of QlikCommunity" series.

In this QlikCommunity Forum thread the poster asked about using the class() function to create a dynamic dimension of 30 minute intervals in a chart. He wanted to format the class values as display friendly time ranges.

My solution would have been to use mapping to format the classes to the desired display format. However, this would have been a lot of data entry for 48 intervals in a day.

John Witherspoon posted a more elegant solution utilizing a simple expression.

dual(time(floor(timestamp, 1/48),'h:mm TT') & ' - ' &
time(ceil (timestamp, 1/48),'h:mm TT')
,floor(frac(timestamp),1/48))

Using John's example, I was able to extend the idea to easily provide for a user selectable interval size.

Read the thread for details.

-Rob

Analyzing Field Usage in a QVW

2009-04-22T17:53:00.000-07:00

Wouldn't it be nice to know what fields are "unreferenced" in your document? By unreferenced, I mean fields that are not used in Expressions, Dimensions or Keys. These are fields that if removed from your document, would have no impact on the visible elements of the application.

Removing unused fields is sometimes important in addressing performance issues and generally makes your application easier to maintain and understand.

The Expression Overview dialog is great for finding where a field is used, but what about the unused fields? I don't know of any QV supplied tool that can identify unused fields, so I created one myself. My tool is a QVW named "DocumentAnalyzer" and it's available for download from:

http://robwunderlich.com/Download.html

First off, let me make it clear that this tool is imperfect. It's difficult to do a precise field usage analysis from "outside" of the product. I hope that this work of mine will encourage (goad?) Qliktech into writing a field usage analysis tool within the QV product. I'll be happy if my work becomes obsolete.

I'll explain the usage and limitations of DocumentAnalyzer as well identify some interesting code tidbits for anyone who may want to enhance or borrow from this app.

The app code itself consists of two pieces. 1) A Macro Module that extracts meta information from the document to be analyzed (the "target" document) 2) A load script that processes the extracted meta data.

The Macro does a lot of filesystem access and requires System Access module security. If you have not allowed System Access, the macro will warn you and provide instructions for setting it.

Using DocumentAnalyzer is a two step process, driven by buttons in the Main sheet. The first step is to choose a target document. Pressing the button will bring up a standard windows file chooser dialog. If a file chooser dialog cannot be created, the user is instructed to type a filename directly in the input box.

Once a target document is selected, press the "Process Document" button. The Macro module will be invoked and extract the metatdata to a series of files in your temp directory. After extraction the load script will read the extracted files. At the end of the load script, the metadata files will be deleted.

The tool will open the target document and navigate through the screens. Keep your hands off the keyboard while this process runs. When the load script is complete, you'll receive a msgbox like this. Press OK.

The first chart of interest can be found on the "Fields" sheet. The Field References chart lists each FieldName and indicates whether the FieldName was used as a Key, Dimension, In an Expression, Macro or Variable. FieldNames not referenced anywhere are highlighted in yellow. These are fields you might consider dropping from the document.

FieldNames that contain special characters are flagged in red. The usage of these fields cannot be accurately determined due to limitations of the parsing method I'm using.

The Exception sheet displays you may consider warnings about the data presented. Of particular interest is the "Unmatched Fields" listbox. Check out the Help in this listbox to understand it's contents.
The Parsing Algorithm
The identification of field rerefences is performed by the load script. The parsing is rather primitive so I'll provide some explanation here so you can understand it's limitations. You can also find this information on the "About" sheet of DocumentAnalyzer.
Field names are discovered in expressions by replacing "special" characters with a delimiter and then parsing into words using the subfield() function. For example, the expression:

"Avg(Price * Quantity) * 1.05"is delimited to:
"Avg\Price \ Quantity\ \ 1.05"which is then parsed into four words -- Avg, Price, Qty, 1.05 . The words are then matched against fieldnames. You will get a false match if a function name such as "Avg" is also used as a field name. If you just want use DocumentAnalyzer, no need to read further. If you want to learn something about the code, read on.

The Code

The Macro writes the metadata to a series of files. The files are normally deleted at the end of the script.
If you want to keep the metadata files, comment out this line in the script (on the Cleanup tab):
LET x = DeleteFolder('$(f)'); // Delete the data files

The Macro Module is of a fair size -- about 500 lines. If you examine the code, you'll find some conditional code (IsInternal()) devoted to allowing the macro to run internally in a qvw or externally from cscript.exe. The entire macro module can be copied to an external file and run with cscript. I coded for "dual" execution environments because I get a better editor and debugger in the external environment. I do the development running externally and then paste the script into the QVW for final testing.

Meta information (Field names, Dimensions, Expressions, etc) are extracted from the target document using the QV API. Getting Dimension values was fairly easy, they are only a few API paths for the various object types.

Finding all the expressions was the most challenging part and took the most time to solve. There are many different properties where expressions may be used in sheet objects. They may also differ by release. I could not see discovering and writing all the API calls to extract every possible expression. I experimented with a number of approaches, including generating code from the API doc -- never got this to work correctly. The most promising approach was using the file export from the Expression Overview dialog. This gave me a complete list of expressions, but the exported file was not consistently usable. The export file is a tab delimited file. If an expression uses tabs or newlines it can make file impossible to navigate.
My eventual solution was to export the objects into XML using the
WriteXmlPropertiesFile outputFile API method and then extract the expressions from the XML files. I first tried to get the expressions using load script, but found this too cumbersome. I settled on using XPath to extract the data I needed from the XML files. XPath is a sort of "query language" for XML. Where SQL returns a set of rows, XPath returns a set of XML elements. This required only a few XPath expressions to cover all the possible expressions.
Once I perfected the XPath method, I switched to doing the Dimension extraction this way as well.
In QV 8.5, Sheets do not have an XML representation. So Sheet expressions (background color, conditional show, etc) are extracted indivdually.
The Document Variables. Macro Module and Script are written to files as well. The Script is not currently processed by the load.
Contact me if you have any problems or questions on using the app. Contact information is on the "About" sheet.
Happy analyzing!
-Rob

BOQC: ApplyMap instead of Join

2009-04-22T17:23:00.000-07:00

Today marks my first blogging of "Best Of QlikCommunity" (BOQC) where I plan to highlight what I find to be particularly useful or interesting posts on the QlikCommunity Forums.

There are cases when the ApplyMap() function is a very useful alternative to Join. For some time I have noticed both Oleg Troyansky and Michael Nordstrom dropping this hint on QlikCommunity but I never quite understood the power of the technique until a post Oleg made today:
http://community.qlikview.com/forums/p/16017/62222.aspx#62222

The original forum question was how to multiply two fields from different tables to derive a new calculated field. The tables share a common key field.

I usually would have approached this with two Joins. That approach works, but sometimes I don't really want my data model to reflect Joined tables. I just want to do the calculation.

If you want to see the ApplyMap() solution, read the post linked to above. The thread explains it better than me repeating it here.

Effective Visual Communication

2009-04-20T20:35:00.000-07:00

Now you own a brand new power saw (Qlikview). But do you know how to build a house?

A key feature of Qlikview is graphical representation of data. Do you have the skills to use that capability effectively? The challenges of effective visual communication are not unique to Qlikview. The principles of visual communication can be learned and there are many resources available to learn from.

An important and useful work to me has been the book "The Visual Display of Quantitative Information" by Edward R. Tutfe (ISBN-0961392142). In this book, Tufte defines and articulates many important principles of statistical graphical representation, including the "Lie Factor" "Chartjunk", and the "Data-Ink ratio". The principles of Chartjunk and the Data-Ink ratio ask us to consider which of these two charts communicate more effectively.

Tufte has authored several more books that expand and illuminate these themes of quality and effectiveness in graphical communication. All of the books are beautifully printed. Tufte also teaches seminars and maintains a web site at: http://www.edwardtufte.com/tufte/

Another work I have found very useful is "Information Dashboard Design" by Stephen Few (ISBN-0596100167). This book applies visual communication principles from Few, Tufte and other researchers to the practical application of "dashboard design" . Few's book analyzes the shortcomings of some poor designs and then goes on to enumerate a number of elements for effective designs. He closes the book with some examples of optimal designs.

Stephen Few also teaches public workshops. His schedule and blog can be found at http://www.perceptualedge.com/.

Lastly, the IBM Many Eyes project http://manyeyes.alphaworks.ibm.com/ is an ongoing source of inspiration and amusement.

-Rob

Using MapSubstring() to edit strings

2009-01-09T00:45:00.000-08:00

The MapSubstring() function is a powerful alternative to using nested Replace() or PurgeChar() functions.

MapSubstring(), unlike it's siblings ApplyMap() and Map, will apply multiple mappings from the mapping table. Here's an example.

ReplaceMap:
MAPPING LOAD * INLINE [
char replace
) \
) \
" \
, \
/ \
- \
] (delimiter is ' ')
;

TestData:
LOAD
*,
MapSubString('ReplaceMap', data) as ReplacedString
;
LOAD * INLINE [
(415)555-1234
(415)543,4321
"510"123-4567
/925/999/4567
] (delimiter is ' ')
;

In field "ReplacedString", all the characters matching the first field of the map ("char") are replaced with a backslash as shown in this table. This makes it ready for parsing with a function like SubField().

Another usage is an alternative to nested PurgeChar() to remove multiple characters. A blank is used as the mapping character. For example:

PurgeMap:
MAPPING LOAD * INLINE [
char replace
)
)
"
,
/
-
] (delimiter is ' ')
;

MapSubString('PurgeMap', Data) will produce results like this:

-Rob

The design of grouped objects

2009-01-05T11:32:00.000-08:00

I recently submitted a feature request to Qliktech requesting "grouped objects". There are a number of issues to consider when thinking about how grouped objects should be implemented in QV.

You are probably familiar with the idea of grouped objects. It's a feature commonly available in graphics authoring programs Select a set of objects and mark them as "grouped". Operations such as Move or Resize then apply to the group.

The problems I'm trying to address by asking for grouped objects:

When two or more objects are logically related, it's would be useful if they could be minimized or restored together.

It's a common practice to layer objects upon one another to produce an effect that is not available with a single object. For example, Stephen Few's Bullet Graph is commonly implemented in QV as a chart upon a chart. The Qliktech demo "Finance Controlling" uses this technique. Another common use case is placing a transparent text box on a chart to provide helptext, or a button to provide action.

Currently, if you layer objects, you also have to prohibit the user from moving or resizing the objects. If not, user move or resize operations may cause the objects to be misaligned.

If QV would let me select a set of objects and mark them as a "group", how should the group of objects respond to various user actions?

Let's first consider a group of two objects that are related, but do not overlap on the screen. For example, a straight table and text box. The text box contains some summary data from the table. Here's are some possible behaviors:

If the user moved any object, all objects would move as a group, maintaining relative position to one another.
If the user resizes one object, all objects will resize proportionally and maintain position relative one another.
If the user maximizes one object, the group will enlarge proportionally to fill the screen.

Let's consider another case. Two overlayed bar charts are used to create a Bullet Graph. One chart also contains a button in the caption area. It's critical that the overlayed charts maintain position and size relative to one another. The button is a fixed artifact that should not resize but must remain in the "correct" position. How should the behavior differ from the example above?

If one of the charts is resized or maximized, both charts must resize. The button should not resize. This could be controlled by use of the "allow move/resize" property of each object. If the property is checked, the object will participate in a group resize. If unchecked, the object will not resize with the group. However, all objects will maintain relative position.

What specifically, is "relative position"? I define it as the "x,y pixel distance from the nearest corner of a parent object. The parent is the object in the same group who's corner is closest to the child object". An object can only be relative to one parent object. The parent may change after a resize operation.

It would simplify the grouping process if the "Allow Move/Size" property were split into two separate properties -- "Allow Move" and "Allow Size". In the chart & button example, all objects would "Allow Move" but only the charts would "Allow Size".

Your comments and improvements on this proposal are welcome.

-Rob

Update 01/09/2009: What about Object "Print" and "Copy To Clipboard -> Image" functions? My proposal would be to include all objects that share a pixel with the right-clicked object.

Extracting data using Microsoft Logparser

2008-12-11T10:29:00.000-08:00

I've fielded several questions lately about loading Group membership information from Active Directory. The Active Directory sample in the Qlikview Cookbook uses AdsDSO and can load "single-valued" fields such as Name and Mail. AdsDSO will not load "multi-valued" fields such as "memberOf" or "member" -- fields that define group members.

To read multi-valued fields, you'll have to install some sort of tool to extract the data into a format QV can load. There are a number of free and commercial utilities available that will extract AD information into a text file.

My favorite tool for AD extracts is the free Microsoft Logparser. Google for it, you'll find lots of information as well as the download link. There is also a Logparser book and forum available.

Logparser can read data from many different inputs -- Active Directory, IIS logs, Windows Event logs, Registry -- to name a few. Logparser can write to several different output formats, CSV being the most useful for QV.

Logparser uses a SQL syntax for it's queries. Here's an example:

logparser -objClass:Group "select cn, member into tmp\AdGroups.csv from LDAP://mydomainController"

This Logparser query will create the output file "tmp\AdGroups.csv". The file will contain one row for each group (cn). The members of the group will be returned as a single field with the members separated by the pipe "|" character. The members are easily separated in the QV load using the QV subfield() function:

subfield(member, '|') as member

Other uses I've found for using Logparser with Qlikview:

Extracting data from Windows Event logs.
Preprocessing IIS log files. The fields contained in a IIS log can vary between sites and may also change dynamically within the same physical file. Logparser can neutralize these differerences and produce a common input for QV load.

Logparser is a favorite tool of mine. I use it frequently for non-Qlikview tasks as well.

-Rob

Update 12/12/2008 I've published a complete example of using logParser to extract Group and User data from AD for loading into QV. The example is in version 9 of the Qlikview Cookbook available at http://robwunderlich.com/Download.html

Using the Evaluate API in Macros

2008-11-19T16:43:00.000-08:00

A common application of QV macros is to make initial selections. These selections may be applied when the document is opened, a sheet is activated or a button is pressed. The selections usually involve some type of dynamic value, such as "today()".

In the examples below, the VBS continuation character "_" is used to indicate the statement continues on the next line. You may type the statement on a single line if wish and omit the "_".

A typical macro to select yesterday's date looks like this:

ActiveDocument.GetField("ShipDate").Select _
Date()-1

It's all VBScript, but the functions used come from two different products. The ActiveDocument...Select is from the QV API, and the Date() function is a VBScript function. To make this work, you need some knowledge of VBS functions. In addition, there may also be a type mismatch -- the VBS function returns a number and the Select expects a string. Fixing that issue requires adding a CStr() or CDbl() function.

What if you want to make a more complex selection. Like the weekday for today? Or a selection that requires knowledge of the data values -- max(ShipDate)?

Wouldn't it be easier if you could use QV Expressions to define the selection expression? You can, with the ActiveDocument.Evaluate API function. Here are some examples.

ActiveDocument.GetField("ShipDate").Select _
ActiveDocument.Evaluate("date(today(1)-1)")

ActiveDocument.GetField("Weekday").Select _
ActiveDocument.Evaluate("weekday(today(1))")

ActiveDocument.GetField("ShipDate").Select _
ActiveDocument.Evaluate("max(ShipDate)")

The Evaluate argument is any expression that can be evaluated by QV. So you can stick with the QV functions you are used to. Those functions are also more likely to produce the correct data type for your selection. And most importantly, you get easy access to the QV data -- ("max(ShipDate)").

You can even use QV search operators such as ">". For example, to select the last 7 days:

ActiveDocument.GetField("ShipDate").Select _
ActiveDocument.Evaluate("'>' & date(today(1)-7)")

I hope you find this tip useful.

-Rob

Qliktech Communication

2008-11-04T17:46:00.000-08:00

I'm receiving new communications from Qliktech, and I like it.

Jason Long, webmaster of the QlikCommunity site, has engaged community members in a dialog soliciting suggestions for improvements to the Forum. Jason has already implemented a number of requests as well as the very cool Google forum search engine.

I'm now receiving a monthly email from John Trigg, Qlikview Developer Product Manager. The email acknowledges the product enhancement requests I submitted in the prior month. I assume other customers who have submitted enhancement requests are receiving an email as well. This a vast improvement to the old "black box" submission process where I was left wondering if anyone ever read my request. Thanks John!

-Rob

The match() Function

2008-10-25T12:58:00.000-07:00

In SQL, the "in" operator is commonly used to test if a value exists in a list of values. For example:

SELECT * FROM TABLE WHERE CODE IN ('a', 'b', 'f');

New QV developers often spend some time looking for QV's equivalent of the "in" operator. It's the match() function.

LOAD * WHERE match(CODE, 'a', 'b', 'f');

The match() function, and its siblings "mixmatch" and "wildmatch". are documented in the "Conditional Functions" section of the Ref guide and the help:

match( s, expr1 [ , expr2, ...exprN ] )
Compares the string s to a list of strings or string expressions. The result of the comparison is an integer indicating which of the comparison strings/expressions matched. If no match is found, 0 is returned. The match function performs a case sensitive comparison.

mixmatch() works just like match() except it does a case insensitive comparision.

wildmatch() is another form that can be particularly useful. wildmatch() allows (but does not require) the "?' and "*" wildcard characters in the match arguments.

wildMatch(text, '*error*')

will match:

"An error has occurred"
"Error processing account nnnn"

QV 8.5 provides a "like" operator that allows for testing against a single value with wildcards:
text like '*error*'

Wildmatch can test against multiple values:

wildMatch(text, '*error*', '*warning*')

The match() functions return a number indicating which of the comparison strings was found. You can use this index number nested in a pick function to do "wildcard mapping" as an alternative to a nested if() function.

pick(
wildmatch(PartNo,
'*99', 'P1586', '?15*', '?17*', '*'
),
'Taxes', 'Premium Fuel', 'Fuel', 'Lubricant', 'Other')

-Rob

An Example is Worth a Thousand Thread Replies

2008-10-20T20:08:00.000-07:00

There's a lot of information being exchanged on the QlikCommunity Forum http://www.QlikCommunity.com these days. Customers and Consultants ask technical questions, and other Customers, Consultants and QT employees provide very useful answers. Today's post is a tip on how to improve the chances of your Forum question being answered quickly and accurately.

Many back and forth replies to a forum thread are about clarifying the question. If possible, post a qvw file example with your question. (I can't, my file is too big! The data is private! Keep reading for ways to handle these concerns).

Reasons to post an example qvw:

An example will help clarify your question. The Forum is conducted in English, but English is a second language for many, if not most, of the Forum users. An example will provide additional understanding of your question.
More likely to get an accurate and complete response. Many questions require the responders to fiddle with expression or script syntax. If I have a qvw to work with, I'm more likely to test my answer before posting it, saving you the trouble of learning that I forgot a comma in my recommended solution.
Time. Most Forum members answer questions on a volunteer basis and their time is limited. For myself, I can only take the time to answer a limited number of questions. I'm more likely to pick the questions that are clear and provide the data I need. If I have to code up my own test data to work on the problem, I'm less likely to respond.

Some of the reasons you may be reluctant to post your qvw -- size and privacy.

The maximum attachment size allowed on the Forum is 1MB. You can make the example qvw smaller by using the QV Data Reduction feature.

Make some selections to reduce the number of selected values in the qvw.
From the menu bar, select File->Reduce Data ->Keep Possible Values.
Use File->Save As to save the reduced copy under a new name.

If you use "Save", QV will still open the "Save As" to help you remember not to overwrite the master copy.

You can protect the privacy of sensitive information, such as account numbers, revenue or customer names by using the QV Scrambling feature. In the menu bar, select Settings->Document Properties->Scrambling.

Here you can select a field to scramble and press the "Scramble" button to perform a random scrambling of the field . No one can determine it's original contents. Like values will scramble to the same value which maintains the value linkages.

In some cases, you may still be unable to post your qvw even with reduction and scrambling. Or it may make your example more clear to post the data inline with your question. In that case, post your example data in the question using comma delimited format, so it can easily be pasted to a LOAD INLINE. For example:

Accounts:
AccountNo, Name
1234, ABC Corp
4567, DEF Co

Transactions:
AccountNo, TranId, Amount
4567, 1, 2000

One last tip. Before you post, remember to search for existing answers to your question. In the past, search on the Forum was not so robust. But QT has recently added an embedded Google search feature. This is great! It supports the full range of Google search operators. Try it. The "Google Custom Search" link is available at the top of each Forum page.

Finally, don't forget to mark your question as "solved" when you've received a satisfactory answer.

Happy posting!

-Rob

Reducing the Need for Macros

2008-10-09T17:21:00.000-07:00

Many QV Developers create macros to extend the functionality of a QV document. Using macros brings up a number of concerns, including performance, stability and maintainability. It's better to achieve the functionality using the core product wherever possible.

But...you can't always get the results you need using the core product features. Here's my wishlist of enhancements to the core product that would reduce the amount of necessary macro coding.

1. Sheet Object "OnDocumentOpen" expression. This would allow an expression to establish a default selection in an object when the document is opened. For example, a calendar or listbox might use the expression "today()" to open to the current date. I see a lot of macros and questions on the QlikCommunity Forum doing this exercise.

There are some design considerations, such as what order these "selections" will be applied. I'm sure these details can be worked out.

2. "Enable Expression" expression. It's a somewhat common requirement to dynamically modify which columns are shown in a chart, based on selections and conditions. I've seen various approaches employed to meet this requirement. One method is to define all the expression columns in the GUI and use the macro API to toggle the Enable property of each expression. This works, but it's hard to code correctly and customers are developing the same code over and over again.

The Expression "Enable" property in the GUI is currently defined by an on/off checkbox. If an expression was allowed the condition could be expressed there and a lot of macro code would be eliminated.

If you've got suggestions for reducing macros, please leave a comment here or on the QlikCommunity Forum.

-Rob

Loading Multiple Excel Sheets

2008-09-29T11:50:00.000-07:00

Load from Excel is usually pretty straightforward, but sometimes you'll need to load multiple sheets and make some determinations at runtime. Details such as sheetnames may not be known at script creation time.

The QV statements "SQLTables" and "SQLColumns" may be used to discover information about the sheets and columns available in a workbook. Both of these statements require an ODBC connection. The ODBC connection may also be used to subsequently read the data, but I find using the LOAD biff more convenient.

First make a OLEDB connection to the workbook:
CONNECT TO [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=workbook.xls;Extended Properties="Excel 8.0;"];

Specify the workbook name, relative to the current directory, in the "Data Source=" parameter. This example uses a "DSN-less" connection. It does not require you to predefine an ODBC datasource.

The SQLTables statement return a set of fields describing the tables in the currently connected ODBC datasource, in this case the workbook. A "Table" is an Excel Sheet.

tables:
SQLtables;

Now I've got a list of sheets in the QV "tables" table. The field name that contains the sheetname is "TABLE_NAME". I'll loop through the set of TABLE_NAME values and load each one using a standard biff LOAD.

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));
Sales:
LOAD *
FROM workbook.xls (biff, embedded labels, table is [$(sheetName)]);
NEXT

Sheetnames that contain blanks will be surrounded by single quotes. The purgeChar() function above removes any single quotes that may be present in the sheetname.

What if I only want to load those sheets names whose name begins with "Sales"? Wrap the LOAD statement in an IF statement to test the sheetname:

IF wildmatch('$(sheetName)', 'Sales*') THEN
LOAD .....
END IF

How about this case? I want to load any sheet that contains the three columns "Sales", "Year" and "Quarter":

columns:
SQLColumns; // Get list of columns
// Join list with columns of interest
RIGHT JOIN (columns) LOAD *;
LOAD * INLINE [
COLUMN_NAME
Quarter
Sales
Year
]
;
// Create a count of how many columns of interest each sheet has
selectSheets:
LOAD TABLE_NAME as SheetName, count(*) as count
RESIDENT columns
GROUP BY TABLE_NAME
;
// Keep only the SheetName that have all 3 columns
RIGHT JOIN
LOAD SheetName
RESIDENT selectSheets
WHERE count = 3

// Load the selected sheets
FOR i = 0 to NoOfRows('selectSheets')-1
LET sheetName = purgeChar(peek('SheetName', i, 'selectSheets'), chr(39));
LOAD....
NEXT

You may wonder if you could use the Excel Driver instead of the Jet provider like this:

CONNECT TO [Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DBQ=workbook.xls];

The connection will complete and you can use this connection for SQL SELECTs. However, when SQLTables is called, the connection will enumerate tables/columns for all the *.xls files in the current directory.

This provider uses the parameter "DefaultDir=" (default is .) to control which directory is enumerated for SQLTables and SQLColumns calls. The DBQ parm plays no part. You may find this useful as an alternative to using a traditional "for each filelist..." loop to process multiple files.

Complete text of the examples presented here can be found in the QV Cookbook at:
http://robwunderlich.com/Download.html)

Using the Google Chart API inside Qlikview

2008-09-24T16:14:00.000-07:00

I recently had a requirement to create a heat map of the US States and set about exploring if there was an easier way than creating a QV scatter plot mapped over an image.

I found the Google Chart API. With some help from the QlikCommunity forum, I got a fairly pleasing result.

The Google Chart API is provided free by Google. You pass in an http request with parameters that describe the data and desired layout, and a chart image is returned. I won't cover the details of the API, it's well documented at http://code.google.com/apis/chart/. Rather, I'll share my experience integrating it with QV.

Here's a screenshot of my results. The map will update as selections are made in the Sales chart. The generated map is not clickable. It's just a static image.

A working QVW of the above may be found in the Qlikview Cookbook at:
http://robwunderlich.com/Download.html

The map is a Straight Table with a single expression. The expression is the http://... string used to generate the map. The representation for the expression is set to "Image". Thanks to Tom on the forum for showing me this technique.

The OnAnySelect document event is used to trigger a "showMap()" macro that creates the variables needed for the http string. In a production application, you would want to be more selective and use field level events on the fields relevant to the chart.

In addition to chart layout parameters, the http string contains two parameters that describe the data.

chld= provides the list of states
chd= provides the data values for the states

The States and Values are associated by ordinal position in the respective lists. I could not find a way to keep the lists in sync by using QV expressions alone. The solution was to use the Sales chart as my "data source". The macro walks the rows of the table to build two variables -- the State codes and the Sales values. Here's a snippet of the macro code:

set obj = ActiveDocument.GetSheetObject("CH01")
' Collect the locations
locations = ""
for RowIter = 1 to obj.GetRowCount-1
locations = locations & obj.GetCell(RowIter,0).Text
next
ActiveDocument.GetVariable("vValues").setContent values, false

Another area where VBScript was useful was in encoding the data values as specified by the API. I chose the "simple encoding" method. The Sales values are translated to relative values within a range of single characters defined by the API. The doc http://code.google.com/apis/chart/#simple provided a javascript encoding example which I converted to VBS.

The encoding algorithm requires that the maximum value of the dataset be known to properly spread the individual values across the relative range. To determine the maxvalue in the macro, I use the QV evaluate() function to "callback" to the QV expression language.

maxValue = ActiveDocument.evaluate(
"max(aggr(sum(Sales), State))" )

Producing a chart with the Google API does have some downsides. The user must be connected to the internet and the chart will render slower than a native QV chart. It also does not provide for making selections in the chart and tooltip values like a QV chart does. But I found it to be a simple solution to my requirement. I hope that someday the QV product will provide regional maps as chart types.

Update October 3, 2008: Alistair on the QlikCommunity forum has posted an example of calling the Google Chart API without using macros, which I find to be the preferred method:

http://www.qlikcommunity.com/575/?tx_mmforum_pi1%5Baction%5D=list_post&tx_mmforum_pi1%5Btid%5D=3763&tx_mmforum_pi1%5Bfid%5D=9

The next update to the Qlikview Cookbook will include the "macro-less" technique.

Gripe re Bug Reporting & Research

2008-09-11T21:07:00.000-07:00

Love that Qlikview product. Love that Qliktech support staff. Hate that bug communication.

In my last post, I described a bug encountered on a QV Server upgrade. How many customers should be warned of that bug? Under what conditions would the bug be suffered by other customers? All customers upgrading to the target release? Those coming from a specific release? A specific configuration? Are you impacted by this bug?

Answer: you don't know. Qliktech probably does, but you -- the potentially impacted customer, do not. Unless we call or email our support rep, one at a time, and ask just the right question.

I've been nagging Qliktech for some time now about online bug reporting and more importantly -- a searchable bug database. I've been told that it's "coming" for some time now.

What I want is:

To be able to submit and track a tech support ticket through a web interface.

That authorized users in my organization can view the content and status of those tickets.

To be able to search a moderated database of all tickets/bug/solutions/workarounds submitted by all customers.

Are these cutting edge requirements? No, I get these facilities now from all my other enterprise software vendors.

In my current organization, there are two part time QV Admins providing support, a dozen or so developers, a few managers who may want to be kept informed and sometimes a key user who is impacted by a problem.

The current Qliktech support is via phone or email conversation. That means the staff person working the problem has to continually forward the ongoing conversation to all the interested parties in the Enterprise.

A new problem? Has it already been reported and sufficiently documented by another customer? Is there a workaround? I don't know. So I'll take the time to create documentation and maybe a clear example for support. Only to find "we already know about that one". Or I'll make the quick inquiry and get the response "we need more detail". It's a guessing game. It's slow. It's inefficient.

I want to make it clear that I'm satisfied with the quality of the support representatives and the developers I've had contact with. My complaint is not about people, it's about tools. Having those tools would make both the customer and Qliktech's job easier.

QV Server 8.5 Upgrade

2008-08-27T09:52:00.000-07:00

Last week I upgraded my Server (QVS) from 8.20 to 8.50.6091 and Publisher (QVP) from 8.20 to 8.20.6085.

I don't have test server licenses, so both upgrades were done to production servers in the evening when few users were active.

QVS
After confirming I had current backups, I uninstalled the previous version and installed the new version. As stated in the documentation, the server primary folder needed to be respecified.

Only one problem has been encountered, and it's significant. Server collaboration objects, including server bookmarks, created on 8.2 were no longer visible on 8.5! New objects can be created, but the old ones are "gone". QT support has identified this as a bug and I hope to receive a fix soon, including recovering the 8.2 objects.

Update 8/28: QT expects to provide a fix for this "mid to late September".

QVP
After confirming I had backups of the server files, I shut down the services and performed a backup of the SQL Server QVPR repository. This was followed by an uninstall and install of the new version.

Two problems occurred at startup.

1. As expected, I had to reconnect to the repository (SQL Server QVPR database) at startup. When I tried to connect to the SQL Server, the connection failed with
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

The Execution Service and the Directory Service were both using domain accounts, per the documentation. The CommandCenter Service was using a local account, so it appeared to me that it must be trying to connect using the CommandCenter credentials. I modified the CommandCenter to use a domain account, but then could not get the ControlPanel page to display again. I was getting an asp.net error page -- something about could not connect to localhost:8081 (I didn't record the message and can't recreate it).

I worked around the problem by using a SQL Server account to make the repository connection. Once the connection was established, I could change the Repository Settings screen back to "Use Windows authentication" and all works fine.

2. The document links in the Access Point page were missing a "/" character.

The generated links: qvp://serverfolder/doc.qvw
were missing a "/" between the server and folder names. I fixed this by adding a "/" to LinkMachineName in settings.xml in
C:\Documents and Settings\All Users\Application Data\QlikTech\Publisher\AccesspointService

<AddQvs Port="4747" LinkMachineName="(FromRequest)/"

My understanding is that a patch release is due out in early September.
Update 8/28: QT expects the patch release in "mid to late September".

Memory sizes for data types

2008-05-29T10:23:00.000-07:00

An earlier post of mine When less data means more RAM discussed the ways in which storage ("Symbol" space) needed for field values can increase depending on how a field is loaded or manipulated. This generated some followup questions on the QlikCommunity forum about the optimal storage sizes for fields of various data types.

What's presented below is information gleaned from the documentation, QT Support and experimentation. The numbers come from the document memory statistics file. I hope someone from QT will help me correct any errors.

QV fields have both an internal and external representation. There is a video "Datatype Handling in Qlikview" available on QlikAcademy that explores this subject.This post is concerned with the internal storage of fields.

Numbers

I've found that the storage size appears to be related to the number of total digits. Storage size in bytes, for various digit ranges:

1-10 digits, size=4
11 or more digits, size=13

The above sizes assume that the internal storage format is numeric, which is usually the case if loading from a database. Numbers loaded as text such as from a text file or inline, may be stored as strings which will occupy different sizes.

Dates, Times and Timestamps

Different Database systems provide various degrees of precision in timestamps and I assume the ODBC driver is also involved with the exact value provided to QV during the load. QV times are the fractional part of a day, using up to 9 digits to the right of the decimal point.

- Best size for a Date, 4 bytes.
- Best size for a full Time, 13 bytes.
- Best size for a full Timestamp, 13 bytes.

These sizes can increase when the field is manipulated. Want to get the date portion of a timestamp? Don't use

date(aTimestamp)

date() is a formatting function, it doesn't "extract" the underlying date portion. In many cases, it actually increases storage size because the result may be a string. Instead, use

floor(aTimestamp)

this will produce a 4 byte integer result.

A common technique for reducing the memory footprint of timestamps is to separate the timestamp into two fields, integer date and fractional time. You can further reduce the number of unique time values by eliminating the hundredths of seconds, or even eliminating the seconds if your application is ok with minute precision.

Strings

Thanks to QT support for providing this detail on Strings.

"The representation is that each symbol has a pointer (4/8 bytes on 32/64-bit platform) + the actual symbol space. This space is the number of bytes (UTF-8 representation) + 2 (1 is a flag byte and 1 is a terminating 0) + 0, 4 or 8 bytes that store the numeric representation of the field."

So on the 32bit version, a non-numeric string occupies 6 bytes more than the length of the string itself. A numeric string occupies 10 more bytes. For example:

"a" uses 7 bytes
"1" uses 11 bytes

The only way to reduce the string footprint is to reduce the number of unique values. This can be done by breaking the string into component parts if that makes sense in the application. For example, the first 3 characters of a 10 character product code may be a product class. Breaking the field into ProductClass and ProductNumber fields may reduce the number of unique values.

If the strings are keys that don't need to be displayed, the autonumber() or autonumberhash128() functions can be used to transform the values to 4 byte integers. With these functions you can also get the "sequential integer optimization" which reduces the symbols space to zero.

I've found that concatenating fields in autonumber like
autonumber(f1 & f2)
can sometimes produce false duplicates. Better to instead use autonumberhash128 like
autonumberhash128(f1, f2)
This seems to always produce correct results.

Sequential Integer Optimization

For each field, QV maintains both a Symbol table -- the unique values of a field -- and a State array that tracks which values are selected. If the symbol values are consecutive integers, a very clever optimization takes place. The Symbol space is eliminated and the State array is used to represent both selection state and value. This is a very beneficial effect of using the autonumber functions.

The values need not begin at zero for the optimization to take place, they only need to be consecutive. A set of 5000 consecutive dates will occupy no Symbol space. Take one date out of the middle and the storage reverts to the standard 4 bytes for each date.

It's not always necessary to be concerned about memory usage. But when it is, I hope this information proves useful.

64bit Implementation Experience

2008-05-22T00:39:00.000-07:00

When I started using Qlikview, I mistakenly believed I would not need the 64bit version of Server. I thought that because my Analyzer users were using the QV Windows Client, the memory required to hold the document would come from the user's machine. Wrong. When a document is opened from the server, the document is loaded into server memory.

The 32bit Server uses a single 2GB address space to contain all the currently loaded documents. When the number of users increased, and more importantly, the number of concurrent documents, the Server ran out of memory. This unfortunately causes a Server crash, taking all the users down, not just the user that pushed it over the limit. It became clear we needed the 64bit edition.

Upgrading the Server (QVS) to 64bit was easy. It immediately solved the memory issue and allowed for many documents to be used simultaneously with no problem.

QV Publisher (QVP) turned out to be a different story. I initially installed Publisher on the same machine as Server but immediately ran into a problem with the availability of 64bit ODBC drivers.

Any ODBC Driver used in 64bit Windows must be written as 64bit capable. I was using four ODBC data sources – IBM DB2, MS SQLServer, Lotus Domino and SAS. 64Bit SQLServer drivers are supplied with the OS. DB2 64bit drivers are available, but they can be expensive. The sticking point was that there were no 64bit drivers available for Lotus Domino and SAS.

My first step was to move Publisher to a 32bit machine. This turns out to be a recommended practice anyway – host Server and Publisher on different machines. But I also had an application in development that would require 64bit for a full reload. How would I reload this application when it moved to production? I expected I would see more of these applications that required 64bit for reload.

Publisher provides for defining multiple Execution Services (XS) on different machines. XS is the service that performs the reload process. The multiple XS's can be viewed and managed from a single Publisher Control panel screen. This feature allowed me to define an additional XS on a 64 bit server.

My configuration now consists of three servers. A 64bit QVS, one 32bit QVP and one 64bit QVP. The 32bit QVP is loaded with all the ODBC drivers I need, the 64bit QVP has no drivers installed. The restriction in this configuration is that reloads on the 64bit QVP may only load QVDs and other non-ODBC datasources. In some cases, this may require a script to be split into two or more documents. Thus far, this restriction has proven to be only a minor inconvenience. The two reloads can be connected together by utilizing a RequestEDX task to trigger the second reload task.

We chose not to migrate the developer workstations to 64bit due to the limited availability of ODBC drivers and other software. Most of the applications that require 64bit for reload can still be developed on a 32bit machine by loading a limited number of records. We did set up a single shared 64bit workstation that can be used by any developer when they require 64bit.

Migrating QVS to 64bit provides the capacity to support many concurrent documents and users. If you plan to use the 64bit QVP, check on 64bit driver availability as part of your planning process.

When less data means more RAM

2008-05-19T07:20:00.000-07:00

I attended Niklas Boman's excellent Performance Tuning talk at Qonnections in Miami. One of his tuning recommendations was to reduce the number of rows and columns when possible. This will probably always have a positive impact on chart calculation time, but if done incorrectly, reducing the quantity of data can have an adverse impact on RAM usage.

Consider a QVD file with one million rows. The QVD was loaded from a database and contains two fields:

aNum – unique integers, 1M unique values.
aDate – dates distributed equally throughout 2000-2003, 1,460 unique values.

QV stores each of these values as integers, occupying 4 bytes of RAM each. Nice and compact.

Which of the following statements will create a QVW that uses more RAM? Statement A, which loads 1000K rows or Statement B, which loads only 750K rows?

Statement A:// Load all 1,000,000 rows
LOAD * FROM qvdData.qvd (qvd);

Statement B:
// LOAD only 2001+ which should be 750,000 rows
LOAD * FROM qvdData.qvd (qvd)
WHERE year(aDate) > 2000;
Pat yourself on the back if you answered “B”. B will use more RAM! More RAM for less data? Why? Because “B” causes an unoptimized load which results in QV converting the Integer representations of the data to String representation.

QV can load QVDs in one of two modes – Optimized or Unoptimized (more in the Ref Guide). In an optimized load, the RAM image from the QVD is loaded directly into memory. An optimized load is indicated in the Loading message in the progress window. (Note to development: would be nice if the optimized message appeared in the log as well).

In unoptimized mode, the QVD image is “unwrapped” and the data processed discretly. This causes the internal formatting to be lost and the data is stored internally as Mixed. So each “aNum” that previously occupied 4 bytes, now takes 9 bytes. “aDate” now averages 18.96 bytes each.

It's the WHERE clause that forces the unoptimized load. Generally, adding fields or anything that causes a field value to be examined will force an unoptimized load. Examples of unoptimzed loads:

LOAD *, year(aDate) as Year FROM qvdData.qvd (qvd) ;
LOAD *, rowno() as rowid FROM qvdData.qvd (qvd)

Even a WHERE clause that does not reference any field will be unoptimized:
LOAD * FROM qvdData.qvd (qvd)
WHERE 1=1;

How can you tell how much RAM a field is using? “Document Settings, General, Memory Statistics” button will generate a .mem text file that contains a storage size for the “Symbols” (values) of each field. You can view the .mem file directly or load it into a QVW for processing. The 8.5 beta provides a “Qlikview Optimizer.qvw” for just this purpose. I've uploaded this file to the “Share Qlikviews” section of QlikCommunity if you don't have 8.5.

WorkaroundsI've found that I can usually “fix” the field by setting the desired format in the Document Properties and checking the “Survive Reload” box. You can also apply formats in the load script, but I find this tedious if I have more than a few fields. Here are some alternative workarounds.

To create additional fields, use a RIGHT JOIN after the optimized load.
Instead of:
LOAD *, year(aDate) as Year FROM qvdData.qvd (qvd);

Use:
tdata:
LOAD * FROM qvdData.qvd (qvd);
RIGHT JOIN LOAD DISTINCT *, year(aDate) as Year
RESIDENT tdata;

For a subset selection, version 8 allows an optimized load using where exists() if the exists clause refers to only a single field. This means you'll have to generate the desired values before the load using the same field name. Something like this:

//Generate table of the dates we want 2001-2004
LET vStartDate=num(MakeDate(2001,1,1)-1);
LET vEndDate=num(MakeDate(2004,12,31));
DateMaster:
LOAD date($(vStartDate) + IterNo()) as aDate
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate);
// Optimized load of the subset dates
tdata:
LOAD * FROM qvdData.qvd (qvd) WHERE exists(aDate);
DROP TABLE DateMaster; // No longer needed

In some cases, the above example will give you an additional optimization. Something I call the “sequential integer optimization” which I'll discuss on another day.

Worrying about RAM is not always necessary and many times is not worth the effort, especially if it makes your script harder to follow. However, for large datasets, particularly in the 32bit environment, you may be forced to optimize RAM usage. Using the mem files allows you to identify the most productive candidates for tuning.

The QV Reference Guide points out that an optimized load will run faster than an unoptimized load. I think it would be useful to have brief discussion of the impact on RAM usage as well.