Qlik Tips

Saving Space by Nulling Zeros

2011-12-06T20:32:00.001Z

If reducing the memory requirement of your QlikView document is important, this tip might help shave a few bytes that could add up to a significant performance gain.

There are several documents on performance improvement that will suggest that you should shed fields that do not contain data that will be used. My tip is to look at fields that has data that you do use and shed the data in those that you won't!

One example would be a numeric field that may contain a lot of zeros. If the only use of this field is in a sum, then there is no different in that zero being there or not. The only difference would be in a count or avg. Therefore, if you have a lot of zeros in a field that will only be summed, consider transforming them to null() in your load.

...
if(Field1=0,Null(),Field1) as Field1,
...

If you need to use that field in a horizontal calculation, you can use the Alt function:

Alt(Field1, 0) + Alt(Field2, 0)

Another type field that we are often told to look at getting rid of is key fields. They are often highly unique so will take up the most space in the dataset. It is often straightforward to remove them, but what if they are needed? Perhaps as a key link in a 1:M relationship.

For example, say you had an Account table with a key field called AccountID. There is also an AccountHistory table which associates on AccountID. You can't readily join the two tables so you need to keep the key field. However, if there is not an entry for every Account in the AccountHistory table, you don't need to keep the value for that particular AccountID in the field! Null it!

I might create a mapping table:

KeyMap:
Mapping Load Distinct
AccountID, AccountID
Resident AccountHistory;

Then, in my Account table load:

...
ApplyMap('KeyMap', AccountID, Null()) As AccountID,
...

Now, this is not going to be significant over a million records. However, over a hundred million, you will see a difference.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

The Right Background

2011-12-04T22:24:00.001Z

I have talked about color choice recently, but whatever choice you make for the colors you are going to use, the choice of background color is critical.

You may have seen an image like this before:

If you haven't, the question is - which of the 5 squares inside the colored box is the same color as the square outside the box. The answer is that all of the 5 squares are the same color. The only difference is the background.

The rule, therefore, is that when choosing a background, it must be a single color. You can't use any type of "gradient" effect or, even worse, use an image as your background.

So, what is the best color to use for the background?

In the world of software development, most IDEs (Integrated Developments Environments), such as Microsoft Visual Studio, have a default of a white background for the code and darker contrasting text. There have been many debates on the use of white as a background. The main one being that the white is wearing on the eye.

Some prefer black or dark blue with lighter contrasting text. However, there is a visual effect that works against this - well known by photography experts - called Halation. This is the effect of brighter colors seeming to spread when on darker backgrounds and hence look a little blurred. This can cause eye strain.

Some suggest that softer grey backgrounds. However, the issue here is that the softer contrast can have an effect on interpretation of color coding in scripting, and it is difficult to find a right choice of usable colors to offset this. This is a feature that increases productivity in developers, so any reduction in its efficacy is not a welcome.

So, it appears that white might just be the right background color for developers, but this post is not about the best color to use for developing - it is for use in the display of quantitative data.

In an article called "Choosing Colors for Data Visualization", Maureen Stone, of StoneSoup Consulting and formerly of the Xerox Palo Alto Research Center, tells us that most color palettes are designed to be printed on white paper so using a white background is the right choice in digital form. If your presentation is going to end up on paper, then designing on a white background is the right choice.

Further, Maureen tells us that there are perceptual advantages in using white as the human visual system is designed to adapt its color perception relative to the local definition of white and giving a white background gives us a stable definition of "white".

As a last word on this subject, I looked at six of the worlds most popular websites:

All use a lighter background and darker foreground (not necessarily black, blue/purple are popular) and, with the exception of YouTube (which has only changed recently), that color is white. These companies have the resources to spend a lot of money on choosing the right background colors.

Who am I to disagree with them.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Easy Ad-Hoc Analysis in QlikView

2011-12-02T23:55:00.001Z

I was demonstrating server object collaboration recently (the ability for end-user to create different charts and other objects and then share these with other users) to a group of users. For me, it is quite straightforward to create a new chart but as their eyes glazed over, I thought to myself, "there must be an easier way."

So I created a view something similar to this one:

What I have here is 2 charts and a few list boxes (main filter list-boxes in a container). The top two list boxes are for "Measure" and "Sort Order". These are "Data Island" values loaded like this:

Set HidePrefix='%';

Expressions:
Load
%MeasureID,
%Measure,
Replace(%MeasureExpression, '##', '$') As %MeasureExpression
Inline [
%MeasureID, %Measure, %MeasureExpression
1, Total Sales, Sum(LineSalesAmount)
2, Cost of Sales, Sum(COGS)
3, Sales Margin, Sum(Margin)
4, Sales Current Year, Sum({} LineSalesAmount)
5, Sales Last Year, Sum({} LineSalesAmount)
];

ExpressionSort:
Load * Inline [
%SortOrder
Y-Value
Default
];

Now, the expressions table here would probably not be inline. It is more likely to be in an Excel document or in a database and then added to based on user demand. You will note that I have to use "##" and then replace with the "$" as it doesn't work with the "$" in situ (QlikView thinks it is a dollar-expansion). You wouldn't have this problem if the source was an external one.

So, this table is a list of the most common expressions that would be used in this particular document. It could be 2, it could be 100. There is no limit here.

A couple of things to note. I have prefixed with "%" and set the HidePrefix value. This is important because your Expression selection does not then appear in Current Selections - which wouldn't make sense to the user.

The second thing to note is that you need to include the MeasureID field because we will use that later to sort the measures.

My bar chart (although it has a fast-change to line) has a Cyclic group as its dimension so I can choose any of my main dimensions. It has two expressions:

1: $(=FirstSortedValue(%MeasureExpression, %MeasureID))

and

2: if(Count(DISTINCT %Measure)=2,
$(=FirstSortedValue(%MeasureExpression, -%MeasureID)),
Null())

This structure allows me to pick up to 2 expressions (I check for this in the calculation condition of the chart). If only one is selected then the 2nd expression is Null() (I have an equivalent formula for the expression label) and QlikView treats it as if it isn't there - which is what I want.

For the sort order, I override the default group order and set as a descending expression:

if(%SortOrder='Y-Value', $(=FirstSortedValue(%MeasureExpression, %MeasureID)), Null())

If all the other sort options (e.g. Text Value) are not checked, the null in this expression will default to the default sort order of the dimension anyway.

The pivot table (which fast-changes to a straight table) is similarly constructed except I use 3 cyclic groups as the dimensions. This gives the users a lot of options.

Worth having a play with it and seeing what you can come up with.

BTW - this will, of course, work with Small Business edition of QlikView which does not support server objects. Also, users selections can be stored as bookmarks.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Delivering BI to Customers

2011-11-26T17:30:00.001Z

It was great to see a project that I have been involved with being mentioned in the press:

Electricity company pushes out BI tool to customers

It would have been nice to see CapricornVentis being mentioned in the article.

We now have several clients who deliver BI to clients using QlikView. These clients range from:

Utilities - delivering usage information to end users to encourage behavior change, saving energy.

Banking - delivering information to their customers to allow better decision making.

Global Farm Feed Company - allowing farmers in 25 countries to measure feed efficiency.

While none of these implementations is trivial, it is great to work with a great technology like QlikView as the enabler. And looking forward to delivering many more - especially on version 11.

If you need to deliver BI to your customers, feel free to give CapricornVentis a call.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Denormalize for Performance

2011-11-06T23:57:00.001Z

It was gratifying for me to find out, after many QlikView implementations, that most of what I was doing was close to best practice in Dimensional Modelling. I thought that I was mostly just building key tables to fix data connection issues and the denormalizing to clean the data structure and improve performance.

If you are unfamiliar with Dimensional Modelling, it has been around for a very long time - as long as QlikView! Ralph Kimball, one of the early data warehouse pioneers, has literally written the book on the subject and has many great articles on his website:

http://www.kimballgroup.com/html/articles.html

If you are not familiar with relational database theory, the idea of normalization is to remove redundancy. For example, if I have a simple product table like this:

Product Table
ProductID	ProductName	Category
1	BingBong	Bings
2	BingBang	Bings
3	BingBung	Bings

The category name of "Bings" is repeated several times. In a relational database this value is actually stored several times. From a storage point of view, it is more efficient to split the product table into a product and a category table like this:

Product Table
ProductID	ProductName	CategoryID
1	BingBong	1
2	BingBang	1
3	BingBung	1

Category Table
CategoryID	Category
1	Bings

In this case, the numeric ID "1" is stored several times but that takes up a lot less space than the word "Bings" which is now only stored once. It also means that if I need to change the name of that category, I only need to change it in one location, thus improving my data consistency.

This system has been implemented widely. Some architectures that you might come across, such as SAP or Oracle Financials, have thousands of tables, all perfectly crafted to store the data most efficiently in the relational database. Normalization works great for transactional databases like this.

For designers, the problem occurred when people started reading data out of the transactional system, especially for reporting. Because the queries now had to hop across several joins, the performance suffers immensely. So, they started either undoing the normalisation process (denormalising) or, worse, keeping the normalised tables but duplicating the data into other tables so that queries ran quicker - a nightmare for data consistency. Both approaches lead to an increase in the data storage requirements meaning that databases bloat more an more - but we can just throw hardware at that issue, right?

So, what is the best thing to do in QlikView? A colleague of mine who was recently trained asked me about this because of the exercise that was to move the category table's data into the product table, hence denormalizing. "Why?", he asked. "Doesn't QlikView work just as well either way?".

There are a couple of reasons as to why you might do this. And a couple of reasons why you might not.

Reasons for:

In one of his articles, Kimball suggests that such "snow-flaking" (leaving the category out of the product table) "compromises cross-attribute browsing performance". Not only that, it "may interfere with the legibility of the database".

So that gives us 2 reasons:

- Performance
- Improved schema readability

And I will add a 3rd:

- Data size.

In the QlikView underlying data cloud, there will be an association between the product fields and the CategoryID field. There will also be an association between the CategoryID field and the CategoryName field. If I move the CategoryName into the product table (either map or join), I can then remove the CategoryID field and hence remove a redundant set of associations. The CategoryName is not duplicated in QlikView because the columnar database still only stores it once.

Reasons against:

- Performance
- Improved schema readability
- Data size

I'm sure that you will be thinking that there is some sort of error here. How can I have the same set of reasons "against" as I did "for"?

It depends! It depends really on the number of records in each table and the number of fields. For a relationship like product and category, there is probably only a few fields in the category table and it can really make sense to denormalise (it almost always make sense to do this for a table with only 2 fields - using ApplyMap). However, with a couple of tables like, say, Order and Calendar, where Order may have millions of records and Calendar could have ten or more fields, it makes sense to keep them apart and just have the key association on the DateID field. If you did join them in, it would mean that you could drop the DateID field but you would then have associations maintained between each field in the Order table and Year, and Month, and Day, and Week, etc., etc. Many more associations being maintained than before and increasing your data size and possibly reducing your performance. It may also become difficult to see the date fields in the schema whereas they are plain to see in their own Calendar table.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Color coding RAGs

2011-10-28T14:36:00.000+01:00

I have been thinking a lot about color and its use in data presentation recently. Color use is something that is especially of interest to me as I find myself to be one of the 8-10% of the male population who suffers from color blindness. In my case, I have the most common difficulty which is red-green. Essentially what happens is that different shades of red and green become more brown to me and therefore two distinct colors, that have a similar saturation and brightness, may actually appear to be the same to me.

One area where this is particularly significant is with RAGs - Red, Amber, Green indicators which will usually indicate Bad, not so bad, and good respectively.

In this example, I have shown pure red, amber and pure green. For me, while they are relatively easy to spot here, the green and amber colors can look very similar on smaller displays or where they are not right together (like in a traffic-light or beacons on a page).

I always like to use Stephen Few as a reference and he has discussed this in his books as well as this useful article: Practical Rules for Using Colors in Charts.

If we think about it, the situations of good, not so good, and bad are actually a sequence so would fall under Stephen's rule 6:

When using color to encode a sequential range of quantitative values, stick with a single hue (or a small set of closely related hues) and vary intensity from pale colors for low values to increasingly darker and brighter
colors for high values.

So, why should we have to use red, amber and green? Perhaps another solution would be just to use shades of red:

Here we have an almost white color representing good (and white would be a good option too as most people are not looking for good, just for the bad). The range gets darker along with the situation.

Of course, one of the problems that might be raised with you is that people "intuitively" understand RAG and that one needs to conform to the "norm".

This thinking was fresh in my mind while recently passing through a UK airport. They have the normal type of gate signs which they have color coded for 4 statii which are to advise passengers on the status of boarding their flight: nothing happening, go to gate, plane is boarding, gate is closing.

The "nothing" status is represented by no color - it matches the black background and that is probably a good choice. The "closing" status is dark red and this is also a good choice because that indicates (in "western" society anyway) danger and the need to get a move on. However, the "go to gate" is coded in amber and the "boarding" is green. This doesn't appear very logical to me as, if I was some way from the gate, "boarding" would be something much more urgent than "go to gate". I am sure that there is a logical explanation, but this doesn't come across to me.

Here, I think, would be a good idea to use something like white background for "go to gate" and something like amber or yellow for "boarding" - they are logically closer to the red of "closing" and should indicate to the passenger that they should get to the gate asap.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Setting default display format

2011-09-15T22:57:00.000+01:00

Following on from the last post on Setting default sort order, there are other things that we can load up in advance that can be of assistance.

For example, I have a date field called OrderDate which is coming in with a default format of DD/MM/YYYY hh:mm:ss but the time portion has no use to me because it is just 00:00:00 for all the dates.

Now, I could load this using a simple:

...
Date(OrderDate, 'DD/MM/YYYY') As OrderDate,

...

This is what I would normally do. However, what if the data is coming from a very large QVD and I would like to keep the optimized load. Applying a format function like this will prevent the load being optimized. So, I should do the format in the script that creates the QVD - but what if I am not controlling that?

Simple - just as with the load order, the default format of a field is set when you first load it. We can create a simple load like this:

FormatTable:
Load
Num(0, '#,##0.00') as LineSalesAmount,
Date(Today(), 'DD/MM/YYYY') As OrderDate
AutoGenerate(1);

Now, I go ahead and load my Order table (note that I have included a format for the sales value too - you can have as many as you need).

Sometime after I have loaded the Order table, I need to drop the Format Table:

Drop Table FormatTable;

I hope this saves you a bit of time.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Setting default sort order

2011-09-15T22:43:00.000+01:00

Sometimes we load some categorical data (e.g. Country) that we want to display in a specific order other than the default (alphabetical for example). Here's an example that you might have come across:

High, Medium, Low

An interesting thing about QlikView is that it remembers how things are first loaded. We can use this little trick to load a temporary table with just the field that we want in the order that we want. Then, even if we drop this table later, QlikView remembers that order and we can use the "Load Order" sort option.

Here is an example. If I had a sales table like this:

Sales:
CrossTable(Year, Sales)
Load * Inline [
Country, 2009, 2010, 2011
Ireland, 200, 300, 400
Scotland, 100, 150, 300
Wales, 500, 300, 400
England, 800, 700, 800
];

But, rather than the alpha-sort of England, Ireland, Scotland, Wales, I would like to have Wales, Ireland, England, Scotland, then I can load a table just before the Sales table like this:

SortOrder:
Load * Inline [
Country
Wales
Ireland
England
Scotland
];

Then, sometime after I have loaded the Sales table, I can drop the SortOrder table:

Drop Table SortOrder;

Now, I can use the "Load Order" sort:

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Irish crime stats. Sleeping easy in my bed?

2011-08-15T13:28:00.000+01:00

I was interested to see the recent announcement of the quarterly Irish crime statistics for Q2 of 2011 by the Central Statistics Office. The press surrounding this latest information hailed the quarter-on-quarter reduction in crime across all categories (for example, from the Irish Examiner).

So, I thought that I would grab the CSO figures and run them into QlikView. You can download the resulting document from QlikCommunity.

It does indeed appear that the crime figures are down across all categories. Of course, one swallow does not make a summer and neither does a change between one period and another indicate a trend. This is where a control charts come in most usefully - is there a downward trend?

Looking at the national figures in a control chart, it does look like there is a steady downward trend. The latest figures have dropped below the control limits (my limits are based on the 2006-2008 data). There is a certain seasonality about the crime figures so I have added a 4 period mean here. Even that appears to be dropping. Looks like the police are doing a great job.

I thought that I might have a delve around in the figures and thought about which if the different crime categories would be more of a concern to me. For example, I am not terribly worried about public order offences, but I am a little more worried about my car being broken into. I don't worry about homicide (Ireland has one of the lowest rates in the world) but I do have concerns about being assaulted. There are some crimes that are more "personal" than others.

If I break down the categories into the ones that I am more concerned about (assaults, burglary, robbery, theft, sexual assault), I see that these represent 45% of the total figures for the whole data set. Interestingly, this is now 48.6% for just 2011!

When I look at these categories in the control chart, I see that these are not decreasing. They are steadily above the mean line for the last 16 quarters. While some have associated increases with the recessionary times, it appears that the increase started in 2007 - a year before Lehman Brothers collapsed.

So, while the Gardaí (Irish police) are doing a good job in reducing crime in some areas, there are some that perhaps need more attention.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Out of Control? Dublin Climate Data

2011-07-29T11:10:00.000+01:00

Last year, I attended Performance Measure Blueprint training with Stacey Barr. One of Stacey's recommended visualizations was the statistical control chart. Stacey also recommended "Understanding Variation: The Key to Managing Chaos" by Donald J. Wheeler in this regard.

Essentially, the control chart introduces some solid statistical thinking into the analysis of data. Quite often, people might have a bipolar view of variation in data - things are up on last month, everything is good, things are down on last month, everything is bad and we need to start changing the process to make sure that things are up next month! The control chart allows us to see that these up/down variations are part of a statistically expected change and, unless they are outside of the control lines or, repeatedly above or below the mean.

An after-dinner discussion recently, on the probability of having a good/bad summer or a cold/mild winter, led me to think of applying control charts to climate data for Dublin.

I was able to obtain historical data from European Climate Assessment & Dataset and more recent data from MET Éireann (the Irish meteorological service).

The QlikView document that I created is available to download from QlikCommunity and will shortly be available to view on share.qlikview.com.

Some interesting analysis!

There is a perception amongst Irish people (especially after a very unusual period of snow last December) that winters are getting much colder and hence we are likely to have a cold winter again this year.

Looking at the data since 1881 (control is based on average between 1961 and 1990 - this is the comparison period used by MET Éireann), it appears that the Dublin climate has been up and down through the 150 years but has always been within the Control - except for 2010. The average temperature for 2010 made a huge dive south and is now out of control. It would appear that yes, the winters are getting colder and we should all invest in snow chains and shovels that we never needed before.

Looking at the data by month, it appears to be even more apparent. The last couple of winters both have troughs outside the control. I do need a new woolen coat - I might get it cheaper if I buy it now.

An interesting thing about the two troughs in the monthly chart though, they are both representing months that are in one year period - 2010. 2010 had 2 of the coldest months for a long time (December was the coldest on record) so that would be why the year as a whole was so far south of the control line. But those 2 months were not concurrent - they were at either end of the year. Maybe I don't need to get that coat yet?

Perhaps, if I want to look at "winter", I should look at the data in seasons instead. So, in my QlikView document, I created a dimension called Season and one called YearSeason - breaking the data into "traditional" seasons of winter = Nov-Jan, spring = Feb-Apr, etc.

Hmm. This data is every winter from 1960 to 2010 (which includes January 2011). This is quite interesting because it shows that, despite having the coldest December on record, the winter of 2010 was still inside the control. In fact, other cold winters such as 1962 and 1976 also are within the control.

So, how cold will the winter of 2011 be? I can't say for sure but, I do have a good level of statistical confidence that it will be less severe than 2010. The system is still in control and the historical evidence is that it will stay in control.

Maybe I don't need those snow chains - not just yet anyway.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Is it time to replace the Quick Chart Wizard?

2011-07-12T22:21:00.000+01:00

I have been contemplating the Quick Chart Wizard in QlikView recently.

Creating charts in QlikView is very easy once you have a reasonably good grasp of the properties and where everything is. However, for "business" users, this is not always simple. Enter the Quick Chart Wizard. My experience, however, is that the output of the Quick Chart Wizard is almost never an immediately usable chart - you almost always have to then go into the properties to clean it up.

There are, I believe, only a couple of options that need to be set in the Wizard to make it produce immediately usable charts almost all the time. And while we are at it, why not do away with a "Wizard" at all and just have everything visible on one page with a preview of what you are building.

Here is my proposed solution:

What do you think?

Design reasoning:

Dimensions - only 2 (instead of 3 in current Wizard). Vast majority of charts will have 1 dimension. 3 is a rarer requirement so remove from Quick Wizard.

Expression - Only one option (as per current Wizard). Added option to label the expression.

Chart options - reduced to 4 of the most common used (removed Pie and Guage from existing Wizard). Give options on Orientation and Mode (which grey out if not relevant)

Titles - not available in current Wizard but something that users will almost always want to change.

Sort by: I never create a Bar chart that I don't specify a sort order on, so this should be available as an option.

Dimension Limitations: With bar charts, this is something that I probably do more often than not.

Preview Window: Show the user exactly what they are building as they are building it.

I am really interested in all opinions on this. Does anyone think that the existing Wizard has value?

Join the discussion on QlikCommunity.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Alternative to Grid-Style Multi-Boxes in Ajax

2011-07-12T19:31:00.001+01:00

I quite like the "grid style" format for multi-boxes. In a limited width, I think that it is a better format for the drop-downs because you get the full width of the control rather than the truncated width of just the drop-down part in the standard view.

A problem for me is that "grid style" does not render at all in Ajax (I can't understand why!)

I picked up an interesting tip yesterday from QlikView's Kenneth Lewis. How about using a Container control with multiple list boxes? I thought about it and then tried it out. I think that it works quite nicely. By adding several list boxes and then setting the Appearance property of the Container to "dropdown at top", I get a result that works in both QV and Ajax.

It is not a perfect replacement for the grid-style multibox but, in some ways, it is better. I like the fact that you have the list box icons available and it is easier to see the Green/White/Grey.

Have a play with it and see what you think.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Nobody said it was easy ... Configuring LDAP

2011-06-30T13:00:00.000+01:00

Recently I was in discussions with a client where they wanted to authenticate users against a Netscape LDAP server. Of course, I told them that this would be no problem because QlikView 10 now comes with a Configurable LDAP directory service connector. Easy peasy!

The client was satisfied and look like they will go ahead and order the new licenses (yay!) and hardware to implement it on.

Gulp! I still hadn't played with this LDAP connector so I though I better get some handle on it before the implementation went ahead. Of course, there is bountiful documentation on it in the server reference manual (well, just under 2 pages). Luckily I had recently met the genius that is Michael Robertshaw, a Solutions Consultant for QlikView in the Netherlands. With some guidance from him, I was able to get up and running.

I found a distribution of OpenLDAP for Windows and installed it on my PC. Once I had an idea of what I was doing, it was actually quite straightforward (Note - choose BDB database, not LDAP during setup and all will be well!) Once I was up and running, I used a tool called LDAP Admin to add new users and groups.

Now to the QlikView side of things. I had thought that this should be quite straightforward. It wasn't initially, but with the help of Michael I got there in the end. The important thing to know is that LDAP and Active Directory LDAP are not necessarily the same thing. There are a lot of differences. Unfortunately, the defaults for the DSP entries are AD ones so these needed to change. Also, the way that the server is specified is not as easy as I thought it should be, but easy to get right once you know.

I had thought that the correct Path entry for my server should have been:

LDAP://myserver

This is what you will see for AD. However, I also found that you need to specify the base search path in this string for me, with my settings of "mydomain.com", this was:

LDAP://myserver/dc=mydomain,dc=com

Once I added this and the correct username and password (note that the username needs to be the fully qualified name, e.g. cn=Manager,dc=mydomain,dc=com), I was able to think about the DSP settings. These are accessed via the pencil icon beside the password box. Now, this is where the Active Directory defaults will need to be changed. The defaults are:

Account Name : sAMAccountName
Directory Label : DSP1 *
Display Name : name
Distinguished Name : distinguishedName
Group member :
Group object class : group
Id Property name : sAMAccountName
LDAP Filter : (&(!(objectclass=computer))(objectGUID=*))
User member of : memberOf
User object class : user

* the directory label is how you will identify users to QlikView, e.g. in this case - DSP1\username.

With advice from Michael and some testing, I found the best settings for OpenLDAP (other LDAPs may be different - use the LDIF information to help you work it out) to be:

Account Name : cn

Directory Label : MyDomain

Display Name : displayName

Distinguished Name : dn

Group member : memberUid

Group object class : posixGroup

Id Property name : cn

LDAP Filter : (yes, this is blank!)

User member of : memberOf **

User object class : posixAccount

** memberOf (used to check users are in a group) will only work in OpenLDAP if the memberOf overlay is enabled and working.

With these settings, I am now able to use the UserManagement in Enterprise Management Console to query the LDAP directory.

I tested that it was working by creating a new document on my server and used DMS to give access to a user called MyDomain\jdoe (who was a valid user in my LDAP). I then generated a ticket for the user and connected successfully to the document.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

AND Mode in Set Analysis

2011-06-14T11:41:00.000+01:00

Was on QlikCommunity recently and answered a query by Lawrence Chapman - Like for Like

When Lawrence replied, he said that he was looking for something quite reasonable in his chart - only sites that had records for all the 4 years.

I was thinking that this should be quite straightforward and then had to have another think! The thing about QlikView set analysis is that it is just a set of selections. The default selection mode in a field in QlikView is "OR". So, if I select all the 4 years, I will still select all the sites because all of them are in at least one year. Creating a set based in the year field wouldn't work for what Lawrence wanted.

I thought about how I would select those sites if I was just doing it in the layout (which is a great strategy for designing a set). The only way that I can think of doing it is to have a selection box using QlikView's AND-mode.

Now, for AND-mode to be enabled, there are a certain number of criteria that need to be fulfilled. Basically, the field needs to be in a table of only 2 fields and it is the 2nd field (the first is the key field associating the rest of the data set). Also, the table needs to be loaded with the DISTINCT keyword to remove duplicates (have a look in the help file for more info).

Let me create a quick example to show this in action. Here is a sales table:

Sales:
LOAD * INLINE [
Customer, Year, Sales
Customer A, 2009, 111
Customer B, 2009, 111
Customer C, 2009, 111
Customer A, 2010, 222
Customer B, 2010, 222
Customer C, 2010, 222
Customer A, 2011, 333
Customer C, 2011, 333
];

You will note that only Customer A and Customer C exist in all years. However, if I load this data, there is no set that I can create that shows me just the totals for those 2 customers based only on the fact that they have sales in each of the years.

So, I will create an AND-mode field:

SalesYear:
Load Distinct
Customer,
Year As SalesYear
Resident Sales;

Once loaded, I add this field to the layout and select the "AND-mode" option in the List Box properties (this option will be grayed out unless the field fulfills the criteria above).

N.B. At this stage, just after setting "AND-mode" in the list box, you may need to just run a re-load to set the internal flag that makes everything else below work.

Now, I can create a set expression like this:

Sum({<SalesYear={2009,2010,2011}>} Sales)

And it will only show me the sales for Customer A and C.

I could make this more generic by limiting the SalesYear to the currently selected Years:

Sum({<SalesYear=P({$} Year)>} Sales) 

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

It's the discovery, stupid!

2011-05-20T17:20:00.000+01:00

QlikView is Qool. It looks great. It is easy to create great looking content. It is fantastically easy to create content and distribute to multiple users. What could possibly be bad? I love QlikView, I think it looks great.

I was surprised recently to read Jay Jakosky's blog entry entitled, "Can a humble chart object get some love"

Amazingly, after 5 years working with QlikView, this was the first time that I have heard anyone suggest that QlikView displays are anything but fantastically beautiful. Jay makes some valid points about displays and about how QlikView's competitors do things. The real point is that it's not about the display!

You see, 99 times out of a 100, you don't need to think about displays like these. For the other 1 time, you can probably do it another way - probably a much simpler and more effective way.

It's not about the display. It's not about the bells and whistles. It is about the effective presentation of lots of data in an intuitive and easy to use fashion that users love to use. It is about allowing users to navigate around their data so that they can find real answers to real business problems real quickly.

It's the discovery, stupid!

P.S. I'm not really calling anyone stupid just because they have a different opinion to mine - just paraphrasing the famous Clinton camp slogan from the 1992 US Presidential election.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Rules for creating a Key/Link Table in QlikView

2011-05-17T17:16:00.000+01:00

A key/link table is frequently required in QlikView to resolve Synthetic Key or Circular Join issues. It can also help to tidy up a schema that has tables all over the place. The goal is to create a star (and sometimes snowflake) schema with dimension tables connected to a central key table. Unlike in classical data warehousing, the central table doesn't often have the measures - they generally stay in the dimension tables.

There are 3 rules for creating a key table. The first 2 are very straightforward - the last is where you need to use your skill and judgement to create the right result. So, here are the rules:

1. All tables should have a primary key. If a table doesn't have one unique key, derive one using a function like autonumber or autonumberhash256.

2. Break all the existing joins by renaming foreign keys (e.g. in the Orders table, rename CustomerID to O_CustomerID). For a "pure" star schema, there should be no QlikView Associations remaining and all the tables should be standalone. From a pragmatic point of view, it is fine to leave some hierarchical tables associated (e.g. Product to ProductCategory) to have a more "snowflake" schema.

3. Use a mixture of Concatenate and Join to generate the Key table using the Resident data. You will load the previously renamed foreign key with the correct name so that they connect to the right dimension table (e.g. ... O_CustomerID As CustomerID).

For an example, if I have a simple structure with Customer, Calendar, Order, OrderDetail and Product.

Step 1 - Customer, Product, Calendar (DateKey), and Order already have a primary key. In OrderDetail I will create an ID from OrderID and LineNo (we will do a bit of step 2 while we are at it):

...

AutoNumberHash256(OrderID, LineNo) As OrderDetailID,

OrderID as OD_OrderID, // rename order foreign key
ProductID as OD_ProductID, // rename product foreign key
...

Step 2 - Customer and Product are not an issue because they don't have a foreign key. I already renamed my foreign keys in OrderDetail so I need to attend to Order:

...
OrderID,
CustomerID as O_CustomerID,
DateKey as O_DateKey,
...

Now all my links will be broken.

Step 3 - Now I load my key table. I will begin with data from the Order table:

Key:
Load
OrderID,
O_CustomerID As CustomerID,
O_DateKey as DateKey,
...
Resident Order;

Now Join in the Product and OrderDetail keys from the OrderDetail table.

Join (Key) Load
OD_OrderID As OrderID,
OD_ProductID As ProductID,
OrderDetailID,
...
Resident OrderDetail;

Now I have a key table which will connect all my detail. I can extend this by Joining or Concatenating additional tables. For example, I could concatenate data from Purchases that also has Date and ProductID information.

Any of the previously renamed foreign keys (e.g. O_CustomerID) can actually be dropped now - their information is encapsulated in the key table so that keeping them is just duplicating data. I might choose to hang onto them for a while, just to test my relations, but best practice will be to remove them.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Part-to-Whole Time Series

2011-05-12T16:50:00.000+01:00

I recently talked (here) about using pie charts for Part-to-Whole comparisons. As Stephen Few explains in his excellent article, Displays for combining time-series and part-to-whole, you can't really use pie charts like this in a time-series. It just doesn't work.

Stephen suggests the use of line charts (which are always a good idea for time-series) with one line showing the total and then the "normal" line chart showing how the total is broken down.

So, how do I create this type of chart in QlikView?

The easiest way is just to create 2 charts, one for the total line and one for the split values. This works fine - unless you want to minimize the objects or want to put them in a container control. Then you run into problems.

There are a couple of solutions for this that I have been playing with. The first one, which is more "dynamic" for the values in the dimension, does not always work out great. Here it is:

So, how did I do this?

I have 3 dimensions in this chart. The 2nd and 3rd dimensions are the Year and Category - just as they would be for the normal split out chart. The first is using the ValueList function in QlikView. ValueList creates a pseudo dimension containing, as its values, the list of values that you provide in the function.

My calculated dimension expression is:

=ValueList('Total', 'Categories')

So, I have a dimension that contains 2 values. I then turned on the Trellis function for this dimension which achieves 2 separate charts for Total and Categories.

I then use ValueList again in the expression to test which of the values I am at so that I can use a different expression for each of them:

if(ValueList('Total', 'Categories') = 'Total',
Sum(TOTAL<Year> LineSalesAmount),
Sum(LineSalesAmount))

So, if it is the Category then I just use the simple expression. For the total I use Total to get the same value for each category and hence achieving just one line in that chart (although there are actually 3!).

All very clever but there is one flaw with this. Because of the trellis, the axis are going to be the same height. This means that in the bottom chart, the lines for each category are a bit squashed down in the lower half.

So, another solution. This one is a little more "static" on the values because I need to define an expression for each of the values in the dimension. I am not sure that this is such a problem because, in the "real world", many of these dimensions are quite static anyway.

So, I have a simple chart with Year and Category for dimensions. I have an expression using a set for each of the 3 category values like this:

Sum({<CategoryType={"Footwear"}>} LineSalesAmount)

and one expression for the total like this:

Sum(TOTAL<Year> LineSalesAmount)

So I do something that has been in QlikView for years (so this will work in v8.5 where there is no Trellis). In the "Axes" tab of the chart properties I set the option for the Total expression to appear on the Right Position and then turn on the "Split Axis" option - like this:

This gives me a display pretty much exactly as I wanted:

I might tweak the split percentage from the default of 67, but it may not be necessary.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Alternative to Null

2011-05-11T17:13:00.000+01:00

There are lots of Null values floating around in data and QlikView has some interesting ways to deal with it.

For example, we can turn on the NullAsValue option for a field that we are going to load:

NullAsValue A, B;

Any values that were previously Null (and do not display in a list box) will now display as blank strings.

There is a small problem when I want to do any maths on a field that might have a Null value and that is that any number plus Null is Null. A great way to deal with this is, if I expect there might be Null values in a field, to use the Alt() function.

Alt accepts a list of values and will return the first of those values that has a valid number. So, instead of:

Load A+B as AB, ...

I can have:

Load Alt(A,0)+Alt(B,0) As AB, ...

Now, if either A or B have a Null value, it is treated as zero and my calculation can continue and give a good result.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Better BI Dashboards

2011-04-21T16:18:00.000+01:00

In CapricornVentis, we've been delivering great business discovery solutions in QlikView for several years now. QlikView really helps you build great user interfaces that help people interrogate their data in new ways that they have never thought of before.

Before QlikView, much of my "Dashboard" design work would have been in SalesLogix CRM where we plugged various chart controls into the CRM data to display information such as Sales Pipeline, Performance, etc. Similar things can be achieved with Oracle CRM On Demand Analytics.

No matter what system you use - BI, CRM, Database, you probably have access to some wonderful tools that allow you to present data to users in lots of different ways.

"What do you want to see on your dashboard", says the chart designer. "Everything", says the customer. "OK", says the designer.

Stop! "Everything" is the wrong answer when it comes to dashboard design.

Think of your car dashboard. It doesn't tell you everything that is happening in your car. It does tell you everything that you need to know, right now, to drive your car.

Think about it. Many car dashboards don't include a clock. A clock is a "nice to have" on a dashboard - it isn't critical to the running of the vehicle.

A "pure" car dashboard probably needs to have only 3 things - a speedometer, a fuel guage and an oil temperature guage. Someone might argue that a tachometer is useful because it lets me know if I am over-revving the engine and also lets me know when is the best time to change gear. We could have the argument but lets just add it in. So, 4 things. Everything else is "nice to have" and you don't need it.

There are other things that I need to know about to run my car. I need to know that my oil levels are good. I need to know that my brake fluid is in between min and max. These are things that I should check every week or so - but I don't need to know about them right now as I drive this car.

It is the same with the business dashboard.

What do you need to know, right now, to run your business?

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Improve load performance by writing better script!

2011-04-20T20:15:00.000+01:00

I came across a document recently where a date was being loaded from a text field in YYYYMMDD format. The developer had used left, right and mid functions along with MakeDate to parse the date.

This is something that I might have done myself several years ago - before I knew better. I wondered to myself if there would be much of a load speed differential between this method and Date# - which is the method that I would use today. So I did some testing.

This particular machine was running v9 so it could have used some load speed tweaking. I took a data set of 3 million records and compared both using the two methods. The left, right, mid method took about 2 minutes (not a fast machine). The Date# method was under 1:30 - 3/4 the time for the other method.

I wonder if you went back and reviewed some of your old code, if you might speed it up a little by just doing things better, just because you now know better.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Blocking user access to a field using OMIT

2011-04-18T14:27:00.000+01:00

This is something that comes up every so often. A question like, "we have sensitive data (such as salary!) in our system that we only want some users to see".

This is all very straightforward on SBS where collaboration is not available. Simply lock down the tab which has the sensitive data displayed using, for example, a condition based on the OSUser() function.

On an Enterprise server, where users can create their own objects, you don't want them to even see that the field is there.

We can achieve this using the OMIT field in Section Access. At a simple level, if you only have one field to hide, you can have the OMIT in the main Section Access table. If you want to perhaps OMIT several fields, then the creation of a group of fields is the thing to do and then just link the user to the group ID.

In the example below, when user1 logs in they will not see Field1 or Field2. When user2 logs in, they will not see Field2 or Field3.

Section Access;
LOAD * INLINE [
   ACCESS, USERID, OMITGROUP
   ADMIN, admin,
   USER, user1, group1
   USER, user2, group2
];

LOAD * INLINE [
   OMITGROUP, OMIT
   group1, Field1
   group1, Field2
   group2, Field2
   group2, Field3
];

Section Application;

Fields:
LOAD * INLINE [
   Field1, Field2, Field3, Field4, Field5
   Value1, Value2, Value3, Value4, Value5
];

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Defending Pie Charts!

2011-04-15T21:13:00.000+01:00

Anyone who knows me (in the BI world) knows that I have a certain lack of respect for the humble Pie chart. I am supportive of Stephen Few's idea that we should, in general, Save the Pies for Desert.

A Pie chart, as it ubiquitously appears in many presentations, used to compare one value against several others, is a very poor choice for the task. A simple bar chart is by far the better choice for the job.

I have been quite happy, up till now, to reject any use of a pie chart for any purpose. That was until I came across Robert Kosara's blog entry, In Defence of Pie Charts. Robert discusses Spence and Lewandowsky's study which shows that pie charts were interpreted more accurately than bar charts in some circumstances.

So, perhaps there is a good use for the Pie chart after all - not in comparison of multiple values, but in simple part-to-whole comparison.

Take, for example, the following Pie chart where we are comparing the performance of Germany versus all other countries.

Now, this chart doesn't give me any numbers but what it does give me is an intuitive feel for how much of the market Germany has.

Now, compare that to an equivalent bar chart.

Here we have a perfectly formed bar chart where we can get a good grasp of the numbers that are involved. However, if I want to get an idea of what percentage the German total is versus the whole, I have to do a little more work to get the answer - I can't intuit it.

So, for this specific purpose, a Pie chart might be a better choice than the bar.

These charts are easily achieved in QlikView using implicit set analysis (available since version 9).

The chart has no dimension but has 2 expressions. In my case, the expressions are:

   Sum({} LineSalesAmount) 

and

   Sum({} LineSalesAmount)

When I select one or more countries, the first expression will give me the total for the selected countries. The second expression gives me the reverse, the total for all the other values.

The labels for these can be achieved using Concat. For the selected countries:

   ='Selected: ' & Concat(DISTINCT Country, ', ')

For the other countries:

   ='Others: ' & Concat({} DISTINCT Country, ', ')

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Data for Humans

2011-04-14T16:48:00.000+01:00

I am a great fan of Stephen Few. Any of his books are worth reading for anyone who works in the data visualization space.

If you can't get a copy of one of his books right now, then 5 minutes reading his article, "Data Visualization for Human Perception", is a great introduction. The commentaries by Ronald Rensink, Naomi Robbins and Robert Kosara are equally worth reading.

My belief is that the study of human perception to create great data visualization is fantastically interesting. There is some fascinating research going on in the area of pre-attentive processing.

As computers get more powerful and the cognitive psychologists keep going with their ground-breaking research, we really are entering a golden age. I look forward to being a part of it.

Even so, I have a feeling that we will still be using the humble bar chart for many years to come. It is such an intuitive and simple visual data display method. I wonder if the real breakthroughs will, in fact, be in enabling people to work more collaboratively with their data to discover new insights.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Best practice on iPad user interface design

2011-04-14T12:54:00.001+01:00

Thanks to Colin Hancox, who pointed me to this article on the QlikView Blog by Chris Mabardy, Director of Product Marketing at QlikView.

http://community.qlikview.com/blogs/theqlikviewblog/archive/2011/04/13/best-practices-for-qlikview-ipad-user-interface-design.aspx

The new mobile platform coming out in QV10 SR2 is new to all of us so it is great to have good guidance on building apps.

In general, all your old applications will work on the mobile platform. The main considerations for iPad are screen real-estate (1024x768) and gestures instead of mouse. If you expect that users will want access to right-mouse features, you need to turn on the new "Menu" icon in the caption.

After that, you just follow good design principals for screen layout. If you haven't already come across it, I recommend Stephen Few's perceptualedge.com and any of Stephen's books.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Your call...Qlikview Server and AccessPoint

2011-04-14T10:36:00.000+01:00

kaushik said...

I would like to learn more about Qlikview Server and AccessPoint.

A brief guide to QlikView Server (QVS). It is worth noting that the QVS Reference Manual is really good now and explains the specifics of this subject really well.

So, QlikView Server is a version of QlikView that runs on a server! Simple, eh? QVS will load documents into memory on the server. When a client connects, QVS will send enough information to that clients so that they can display the information on their screens. When the client makes a selection, QVS uses the memory and CPU power of the server to process that selection and then sends the screen updates to the client. This means that the client doesn't require to have enough memory and CPU to handle large volumes of data and complex calculations - hence why simple clients such as iPhone and iPad work so well. The client just needs enough CPU and memory to render the screens.

QVS only loads each document once. As each client connects, QVS will apportion them a separate area of memory to handle their data requirements (mostly cache of charts). A "rule of thumb" is that each client will require and additional 10% of the document's memory requirement. So if the document loads into 1GB of memory on the server, each client will require approx. 100Mb of additional memory. As each version of QlikView has been released, their memory handling technology has got better and better and v10 will handle many more clients in the same memory than, say, v8.5.

There are now just 3 main client types (previous versions supported a Java client also). First, QlikView Desktop can be used as a client to access documents from a server. When used as a client, you would notice that buttons such as "Save" and "Reload" are disabled because they make no sense for a client - the server will handle all of that.

The second client is the QlikView plugin for IE. This is an ActiveX plugin that sits inside Internet Explorer. It shares the same code base as QlikView Desktop so the experience is very similar. It is still a "thick" client so does require installation - although it comes with an MSI for automated installation.

The third, an easiest to deploy, is the AJAX client. This doesn't require any installation because it works within a browser such as IE, Firefox, Chrome, etc. As of QlikView 10 SR2, it is also supported in mobile devices such as Safari for iPad using HTML5.

The majority of Plugin or AJAX end users will access their documents via AccessPoint. AccessPoint, by default, takes a user's Windows Authentication information and will be able to present to them all of the documents that they have access to. This means users won't be confused by seeing documents that they won't be able to open. Users can also set "favorites" in AccessPoint so that they can default to seeing their most used documents.

Windows security is the default for QlikView server but not the only method available. By turning on QlikView's DMS authentication (not available in Small Business Server), the task of authenticating the users can be given to any other type of directory service or even a completely custom form.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Your call...

2011-04-12T16:14:00.000+01:00

I am training in some QlikView newbies right now and it makes me really aware about the huge number of things that you can learn in QlikView.

Please feel free to comment below and tell me what feature of QlikView you are interested in learning more about and I can use your comments as the basis for future posts.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Load testing QlikView with JMeter

2011-04-06T11:08:00.003+01:00

Another really interesting session that I attended at Qonnections was on scalability. Here, we looked at using the JMeter tool (an open source product from Apache: http://jakarta.apache.org/jmeter/) to first record a set of interactions and then play them back multiple times with multiple sessions.

This is a really powerful tool. Not just for QlikView but for anyone developing web products.

I can't record all the steps here, but there is a tutorial document and some sample files available from the Community page @ http://community.qlikview.com/groups/qlikview-scalability/forum/default.aspx

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

QlikView Mobile

2011-04-06T10:51:00.004+01:00

I attended a great session at Qonnections yesterday where the new mobile app for ipad was presented.

Up till now, QlikView have had a specific mobile application for iPhone which they then enhanced to a "HD" version for iPad. This client was very different from the traditional QlikView. They also had a Java mobile client that worked on devices like BlackBerry. There was also, at one stage, an Android client - lots of clients to have had to support.

Essentially what has been done with new client is that the existing AJAX client has been enhanced to support gestures on mobile devices. The AJAX client has worked on these devices before but many things didn't work as expected and it was a less than satisfactory experience. With the new gesture support, using your finger instead of a mouse is just simple.

From QlikView's point of view, this move makes a lot of sense. Not having to create an support multiple clients means that they can focus effort on making their core clients even better. From an implementation point of view, there is nothing extra left to do - it works off Access Point same as other clients. All of the security, etc., works with it. It is worth sizing screens to the expected end user tablet size - but that is just best practice anyway.

This is already available for people to have a look at - point your tablet device to demo.qlikview.com. Any of the existing demos will work. Nothing special has been done to them. There are a couple of "iPad optimized demos" which you should find a link to off the main window.

For customers, this will be available in SR2 for QlikView 10 - which is due out in the next week or so.

There is a video available showing some of the new features here: http://www.qlikview.com/us/landing/ipad2

No printing in Ajax

2011-04-05T15:36:00.002+01:00

I have come across this a number of times, most recently on a post on Community.QlikView.com, so I thought it was worth sharing.

If you have any type of printing enabled in QlikView, the QlikView Desktop and Plugin will render the printing and submit to the relevant printer. This also gives facility for Print Preview. This facility is not available in Ajax - a limitation of the thin client.

To have printing in Ajax, any print jobs need to be rendered on the server into PDF and then presented to the user as a download.

The problem occurs if the IT department has "hardened" your QlikView Server. One of the common steps in hardening is to disable the Print Spooler Service. This is often built into a Group Policy rule also.

The problem for QlikView is that during installation of QlikView Server, the installer attempts to install the PDF XChange drivers. This step will fail if the Print Spooler service is not running.

Once you have sorted out the Print Spooler Service, you don't need to re-install QlikView Server. There is a PDF driver available as a separate download in the QlikView downloads area.

Beginners Guide to QlikView Extension Objects : Part 4 - Headers

2011-04-04T12:15:00.003+01:00

Here at Qonnections, I am meeting some interesting folks and having some good chats. Meeting people that read your blog is interesting in itself!

I met Andrew last night and he had been playing around with Extension Objects using my previous 3 steps as a starter. He pointed out one glaring omission - how do you add headers! Doh! I had done it before but just failed to add it to the table example.

Headers are actually pretty easy (like a lot of this stuff). We have already seen the this.Data.rows property. Well the Data class also exposes a HeaderRows property. This has a length value but I can't really see where there would be more than one row. Anyway, we just need to extract the text values from the property just as we have done before:

// Add the Header row
var headerRow = this.Data.HeaderRows[0];

html += "<tr><th>" + headerRow[0].text + "</th><th>" + headerRow[1].text + "</th></tr>";

Simple!

Parameters in Dollar Expansion

2011-03-29T17:13:00.002+01:00

I had come across this subject in the documentation before but never had a use for it - until today.

I was faced with several fields that each had a code and description embedded in them that needed to be parsed out. Generally, I would just use SubField() for this but unfortunately the separator was a period (.) so I couldn't be sure to get the full text out using SubField because the text could contain periods (a period is not a good choice as a separator!)

I decided that the easiest thing to do would be to use the Left() and Mid() functions in conjunction with the Index() function. This started looking like this:

Load

Field1,

Left(Field1, Index(Field1, '.')-1) As Field1.Code,

Mid(Field1, Index(Field1, '.')+1) As Field1.Desc,

...

There were 5 fields involved and I wondered if there were a different way that I could go about it. I re-checked the help file on Dollar Sign Expansion and came up with this:

SET mLefty=Left($1, index($1, '.')-1);

SET mRighty=Mid($1, index($1, '.')+1);

The $1 in this variable is a parameter. So I can pass a parameter to a variable!

Now I can use these "functions" in my code:

Load

Field1, $(mLefty(Field1)) as Field1.Code, $(mRighty(Field1)) as Field1.Desc,
Field2, $(mLefty(Field2)) as Field2.Code, $(mRighty(Field2)) as Field2.Desc,
Field3, $(mLefty(Field3)) as Field3.Code, $(mRighty(Field3)) as Field3.Desc,
Field4, $(mLefty(Field4)) as Field4.Code, $(mRighty(Field4)) as Field4.Desc,
Field5, $(mLefty(Field5)) as Field5.Code, $(mRighty(Field5)) as Field5.Desc,

...

Now this is not something that is incredibly advanced - but I can imagine a lot of possibilities.

Beginners Guide to QlikView Extension Objects : Part 3

2011-01-09T23:23:00.001Z

So, we have created custom extensions that display data from QlikView. However, we need to make that object respond to clicks and make selections back in the QlikView document.

This is achieved using the SelectRow function of the Data object. When we write out our data, we need to make sure that we keep track of what row that data came from so that we can execute an “onclick” function to call SelectRow with that row number. QlikView will then make that selection and refresh the data.

Here is the adjusted code to handle this:

Qva.AddExtension('CVL/BasicTable', function() {

        // Create a variable to hold generated html
        var html = "<table>";
        // Local variable to hold the reference to QlikView
        var _this = this;

        // function to handle users clicking on a row
    window.oncvlrowclick = function(rowIx)
    {
        _this.Data.SelectRow(rowIx);
    }

        // Cycle Through the data
          for (var i = 0; i < this.Data.Rows.length; i++) {
                // get the row
        var row = this.Data.Rows [i];

                // Generate html
                html += "<tr><td onclick='oncvlrowclick("+i+")'>" + row[0].text + "</td><td onclick='oncvlrowclick("+i+")'>" + row[1].text + "</td></tr>";
    }

// Finalise the html
html += "</table>";

// Set the HTML
this.Element.innerHTML = html;

},true);

OK, that is it. Time for you to take over and start creating your own objects. You now have the tools to create an object, populate it with QlikView data, and make it respond to clicks.

Enjoy.

Beginners Guide to QlikView Extension Objects : Part 2

2011-01-09T23:10:00.001Z

In the last part, I showed how to create a really basic extension object. Now, I will build on that to use data from my QlikView application.

I am going to add a sub-folder to my CVL folder called “BasicTable” (so, CVL\BasicTable will be the sub-folder).

I need a definition.xml, icon.png and script.js. The quickest way to build these is to copy them from the previous project and then modify.

The initial contents of my definition will be:

<?xml version="1.0" encoding="utf-8"?>
<ExtensionObject Label="CVL Basic Table" />

Now, we can concentrate on the JavaScript to generate the output.

The QlikView object holder (which we can access via “this” in our JavaScript) defines an object called “Data” which will define the data passed by the object from the QlikView document. The data passed will depend on the Dimensions and Expressions defined in the properties of the object.

The Data object has a Rows property. Each Row in Rows is an array of values. The index corresponds to the number of Dimensions and Expressions. In a simple example, with one Dimension and one Expression, there will be 2 values in the array – 0 and 1. In multi-dimensional or multi-expression objects, the Dimensions will be the first values in the array, followed by the Expressions.

Lets define some code to cycle through the data and present it in a simple html table.

Qva.AddExtension('CVL/BasicTable', function() {

// Create a variable to hold generated html
var html = "<table>";

        // Cycle Through the data
          for (var i = 0; i < this.Data.Rows.length; i++) {
                // get the row
        var row = this.Data.Rows [i];

                // Generate html
                html += "<tr><td>" + row[0].text + "</td><td>" + row[1].text + "</td></tr>";
    }

// Finalise the html
html += "</table>";

// Set the HTML
this.Element.innerHTML = html;

},true);

Now, if you add this object to a document, you won’t see anything. This is because we need to specify the Dimension and Expression first before any data will become available.

It is possible to define a default, of sorts, for Dimension and Expression. You could define an actual name or expression but, not every document might have that name and this could cause problems. If we set a blank default for Dimension and Expression, then QlikView will pick the first dimension and the sum of the first number value in the document as defaults. This gives something to show when the object adds (which might cause other problems!). To do this, we modify the definition.xml file. I am also going to define a default caption here:

<?xml version="1.0" encoding="utf-8"?>
<ExtensionObject Label="CVL Basic Table">
    <Dimension Initial="" />
    <Measurement Initial="" />
    <Initiate Name="Caption.Text" value="CVL Basic Table" />
</ExtensionObject>

By the way, if you do make changes to the definition.xml or script.js files, it is not necessary to close and reopen QlikView to see those changes. Simply press F5 and QlikView will refresh. This is very useful in debugging.

Speaking of debugging, I’m afraid that the best way I have found to debug these JavaScript files is to add alert statements.

In the next part, I will show how to add code to handle when a user clicks in your custom object that will make a selection in your QlikView document.

Beginners Guide to QlikView Extension Objects : Part 1

2011-01-09T22:21:00.001Z

I thought that I might write a quick guide to creating an extension objects for QlikView 10.

What is an extension object? Well, it allows us to create a chart using html and javascript or consume a 3rd party flash/silverlight object. Our chart can use data from QlikView and it can pass click events back to QlikView. Basically, we can create any chart that we want that doesn’t exist already.

You might think that there would be a whole lot of work to do to create this, but it is actually really simple to get a chart up and running and consuming QlikView data.

Extension objects live in the C:\Users\your.username\AppData\Local\QlikTech\QlikView\Extensions\Objects folder for desktop users. Each extension must have a folder for itself. However, you can have multiple sub-folder levels if you want.

The first thing that I will do in the Objects folder is create a sub-folder for my company’s extensions. My company is Capricorn Ventis Ltd. so I will create a folder called CVL. In this folder, I will create a sub-folder for each extension. First one that I am going to create is HelloWorld (doesn’t every example have to start with a Hello World!). Now, under the Objects folder, I have CVL\HelloWorld.

Every extension has a minimum of 1 file associated with it - Definition.xml. This file will define the element, ExtensionObject (which can have other sub elements to, for example, set some default values):

<?xml version="1.0" encoding="utf-8"?>
<ExtensionObject />

That’s it – simple. We will have a look at some additional options for this file in the next part.

With this one file in the HelloWorld folder, I can now see in QlikView (in Web View mode) when I right-click and select New Sheet Object, Extension Objects, I will see my new CVL/HelloWorld object (with no icon) which I can drag onto the sheet and it will create an object. It won’t be a very awe inspiring object (a blank box) but you can set properties (such as caption) just as you would for other charts.

We need to add a couple of things to make this just a little more advanced. First, if we want to have an icon (and you really should), we need to add a 24x24 PNG file called icon.PNG to the folder – any design that you want. Next, I am going to make a small modification to the Definition.xml file:

<?xml version="1.0" encoding="utf-8"?>
<ExtensionObject Label="CVL Hello World" />

This label will be what is displayed in the New Sheet Object dialog – next to the icon defined by the PNG file.

Now that those aesthetics are sorted out, we can make the object actually do something. We need to add a JavaScript file that will define the code that will be run by this object. The file is always called script.js. This js file must define a call to Qva.AddExtension which defines a name and function that will be called when the extension loads:

Qva.AddExtension('CVL/HelloWorld', function() {
// Set the HTML
this.Element.innerHTML = "<h1>Hello World</h1>";
},true);

Note that the name defined in AddExtension is the same as the path to the folder as it would be from the Objects folder. This is required.

This code is very simple – it just outputs some simple html – but it reveals how we can do some much more advanced stuff. If we can generate html, we can output it to our application and do so much! All we need now is some data.

In the next part, I will show you how to consume QlikView data.

Things I like about QlikView 10 - Part 3: Extension Objects

2011-01-07T16:03:00.002Z

This is possibly the coolest thing in QlikView.

We were working with a client a while ago who had a very specific requirement for a visualisation. We worked several options with different show/hide options around bar charts and pivot tables but, while they were close to what was required (close enough anyway), they didn't quite hit the mark.

Now I know that i can build exactly what they want with an extension object. I know that I can do this because I know HTML and JavaScript - I know what I can do with it. I don't even need to go to flash/silverlight, I can work wonders in notepad.

Have you had a go yet?

Things I like about QlikView 10 - Part 2: UI

2011-01-07T15:57:00.002Z

I am quite liking the new container object. My only problem is that I keep forgetting about it and creating loads of separate charts with minimized icons - then it's "Doh!" It is cool though.

Linked objects are starting to save a whole load of time - especially those date list boxes that I will have on most every sheet in a document. Being able to link and then change once is a great boon.

Many of our customers now use Ajax as their primary client. Therefore the WebView option in QlikView desktop is really cool. Being able to wysiwyg in Ajax is great.

Things I like about QlikView 10 - Part 1: Script

2011-01-07T15:48:00.003Z

One of my favourite things about v10 is the changes that they have made to the load algorythm to multi-thread the symbol table creation. While you won't notice it so much up to a couple of hundred thousand rows of data, when you get into the millions it makes such a difference.

I am also a big fan of the new QVConnect process that allows you to use a 32bit driver on a 64bit server. It isn't something that we have run into everywhere but we have had a few customers with Accounting systems that only provided 32bit ODBC drivers. Now, it isn't even a consideration. Yay!

Within the script editor itself, the script syntax checking is taking some getting used to but is starting to save me a whole load of time. I am really enjoying the auto-complete here too.

Do we really need maps for analysis?

2010-11-19T00:46:00.002Z

This is a response to Barry's QlikFix post - http://t.co/q4jPQAs

While interesting that 80% of data may have a spatial component (depending on how you define it), the reality is that the majority of that data does not have a special dependency – it is not really that suitable for display on a map.

A common example for such displays might be something like sales by state. These are often displayed as bubbles geographically positioned over the state in question but do they really help with the analysis? Sure, you may be able to easily see that sales in California are larger than in Oregan, but how do they compare against sales in New York, New Jersey and Rhode Island that may be smudged together on the far side of the map. What might look “cool” on a demo may turn out to be something that is easier to analyse in a bar chart.

One other unfortunate flaw is the level of education that might be encountered. Studies have shown that percentages of schoolchildren in the UK have difficulty picking out their own country versus France or Germany! Can we expect that everyone who needs to analyse European data is actually aware that the boot shaped country is Italy?

Maps can be cool and have some great applications (many of which can be easily done in just Google maps without QlikView). Real world analysis can mostly do without them.

It is often simpler than it looks

2010-11-14T20:57:00.002Z

Often, as we encounter more and more diverse data, we encounter data structures that would appear to call for more and more complex workings to associate them in QlikView. What we need to do though is take a step back and see if there is a different way. My experience is that once I do this, I find that it is often simpler than it looked.

For example, you have 2 or 3 tables that have several fields in common. Some might start down the route of generating complex keys (for example, with autonumber or autonumberhash256) and one or more key tables. A second look might reveal that if we simply concatenate all the tables - even though it appears that they are unlikely candidates for concatenation - then we will suddenly be left with a simple key table and some dimension tables.

Another way of simplifying things that I use all the time is ApplyMap. This is a really useful function (not unlike vlookup in excel) that enables us to map small tables into larger tables. Always look to see if you can do a mapping load.

It is often simpler than it looks.

Access DBs on 64bit

2010-09-30T19:25:00.002+01:00

I have written before about the issues of 32bit drivers on 64bit QlikView. It is great to see that version 10 will get past this but, if you need to get Access data into your server now, on earlier versions, that is not a help.

I am writing this because it is fresh in my mind because I have had the exact problem with a client. They had an old Access database that they needed to get data into on 64bit QlikView. Now, I have a 64bit Access driver because I have 64bit Office 2010 and it works great in QlikView. Microsoft have released this as a separate download so you don't need office installed on your server:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

You don't need newer format Access databases, it works with older mdb files too. Only criticism that I have is that the DataLinks properties box doesn't have an ellipse button to allow you browse for your database. This isn't the most difficult thing to get past, just paste the path into the properties box.

Hope this helps.

Installation ended prematurely because of an error

2010-08-10T11:48:00.002+01:00

I had started having this problem recently and decided to blog the solution because I couldn't find it elsewhere.

This was happening when I went to install QlikView 9 SR5 but it is actually an issue with the Wise installer rather than QlikView specifically. My problem is on Windows 7 64bit.

What happens is that the Wise installer is trying to use VBScript and if it fails then it will give this message. If you log the installer you will see messages like:

DEBUG: Error 2896: Executing action WiseNextDlg failed.

Googling the message led me to try re-registering the VBScript.dll file (both in System32 and SysWOW64). However, that did not help.

Using Process Viewer (sysinternals) I noticed that it was loading the following registry entry:

HKEY_CURRENT_USER\Software\Classes\Wow6432Node\CLSID\{B54F3741-5B07-11CF-A4B0-00AA004A55E8}

This GUID - B54F3741-5B07-11CF-A4B0-00AA004A55E8 - is the GUID for VBScript. However, the entries under it were invalid. There was also another HKEY_LOCAL_MACHINE entry that was valid. It appears that Windows will go for the current user entry first.

I renamed the registry entry under HKCU and re-ran the installer and all is now well.

More on Dual - "Others" in chart using AGGR

2010-06-23T21:28:00.006+01:00

In a previous blog post (More On Dual - http://qliktips.blogspot.com/2009/09/more-on-dual.html) I proposed a method for getting better labels in a Pie chart by using Dual to create the label and the value in the expression:

Dual(Country & ' (' & Num(Sum(Sales)/Sum(Total Sales),'0.00%') & ')' & chr(13) & Num(Sum(Sales),'#,##0'), Sum(Sales))

A problem has been pointed out to me by one of my readers - if the number of slices is restricted (10 by default), then the "Others" does not display correctly:

We can fix this by limiting the number of dimensions using an AGGR statement with RANK in a calculated dimension:

=If(aggr(rank(Sum(Sales)), Country) <= 4, Country, 'Others')

So here, I am checking if the rank of the dimension is less than or equal to 4. If so, just use the Country name, if not, use "Others".

Now, there is still a slight problem with this and the Dual - the name is still blank. So, we need to modify the expression to check for a blank name:

Dual(If(Len(Country)>0,Country,'Others') & ' (' & Num(Sum(Sales)/Sum(Total Sales),'0.00%') & ')' & chr(13) & Num(Sum(Sales),'#,##0'), Sum(Sales))

(It will be blank because there are actually multiple values).

So, all should be well!

Of course, this doesn't just apply to Pie charts (thankfully!) - this method of restricting the dimension using Rank and AGGR can be used in any chart - including Straight Tables!

Use a ramdrive!

2010-04-29T13:26:00.002+01:00

Thanks to Håkan (the inventor of QlikView) for this handy tip.

If you are storing a temporary file to QVD and then reading it instead of using Resident (because it is way faster - something that you may find is improved greatly in version 10), if you configure a RamDisk (there are several options for this) and use that as the temporary location for the QVD, then it will be even faster!

I hadn't come across a RamDisk for many years (I think since MSDOS 6) but this is a very interesting tip if saving seconds is critical to you.

Beta testing QlikView 10

2010-04-29T10:42:00.002+01:00

Just back from Qonnections - the annual QlikView Partner conference. This year it was held in the Fontainebleau Hotel, Miami Beach. Good time was had by all.

We got to hear about the new features coming in version 10. I can't say too much about it now, but there are some pretty cool things in there.

They have had a first beta internally for a couple of months now and beta 2 was launched to partners at the conference (and downloaded by me a few minutes later!)

There should be a public beta release in and around June - but that will depend on how beta 2 goes. Release in the autumn.

This is the longest beta cycle that QlikView have ever run. In fact, they are shortly going to upgrade all their own internal systems (including demo.qlikview.com) to version 10 - really eating their own dog food. Expect version 10 to be the most stable new release ever.

How QlikView stores data

2010-04-16T11:16:00.002+01:00

To become really good at QlikView, you have to really understand how QlikView stores data. This is an introduction that should help you along that road.

If I load a very simple dataset:

Fruit:
LOAD *
INLINE [
Fruit, Color
Banana, Yellow
Blueberry, Blue
Tomato, Red
Tomato, Green
];

And then add both fields as list boxes, I see that there are only 3 fruit listed - even though I have loaded 4 entries. "Tomato" is only stored once by QlikView. Internally, there are binary keys keeping track of the association between "Tomato" along with "Red" and "Green".

In a large data set with a lot of repeating values (especially text values) the fact that each field value is only stored once means that there is a huge saving in the amount of memory needed to store the information. This is why QlikView can store so much data in memory.

Let's add another table of data:

Colors:
LOAD * INLINE [
Color, Red, Green, Blue
Yellow, 255, 255, 0
Blue, 0, 0, 255
Red, 255, 0, 0
Green, 0, 255, 0
Amber, 255, 121, 0
];

I now might think that I have 2 tables of data - both of which have a field called "Color". But I don't! There is only one field called "Color" in our dataset and this field will have 5 values. Internally, the association between each of them and their associated values are maintained with binary keys. It just so happens that one of the values - "Amber" - is not associated with any of the values in the "Fruit" field.

Note that there is probably no direct association between "Fruit" and "Red". The association is indirect. However, because of the smaller data set in memory and the binary keys, the association is very fast.

Hope this is useful!

Radish Bits

2010-04-08T13:31:00.001+01:00

A long-time news group buddy of mine, Walter Shpuntoff, has his own blog that covers QlikView. Well worth the read:

http://radishbits.wordpress.com

Watch your Security Connections

2010-04-08T08:22:00.003+01:00

QlikView security is quite simple to implement - you just need to link the security information to the data and you can enable automatic reduction of the information that a user sees. However, you need to watch out for the data that is linking the user to the data - if it is a text field then all of the values in that text field must be uppercase (you know that all the field names in Section Access must be uppercase too, right?)

For example, this straightforward load will enable Tom, Jane or Bill to login:

Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, TOM
USER, JANE, JANE
USER, BILL, BILL
];
Section Application;

LOAD * INLINE [
LNK, Name, Month, Sales
TOM, Tom, Jan, 12321
JANE, Jane, Jan, 31232
BILL, Bill, Jan, 32131
TOM, Tom, Feb, 34342
JANE, Jane, Feb, 34545
BILL, Bill, Feb, 76575
TOM, Tom, Mar, 32133
JANE, Jane, Mar, 12123
BILL, Bill, Mar, 21321
];

If you enable the "Initial Data Reduction Based On Section Access" in the "Opening" tab of the Document Properties then they will be restricted to their own data.

However, if I make a slight change:

Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, TOM
USER, JANE, Jane
USER, BILL, BILL
];
Section Application;

LOAD * INLINE [
LNK, Name, Month, Sales
TOM, Tom, Jan, 12321
Jane, Jane, Jan, 31232
BILL, Bill, Jan, 32131
TOM, Tom, Feb, 34342
Jane, Jane, Feb, 34545
BILL, Bill, Feb, 76575
TOM, Tom, Mar, 32133
Jane, Jane, Mar, 12123
BILL, Bill, Mar, 21321
];

Now, Jane will not be able to login to this document - because the LNK field has mixed case!

Best advice is to use an integer where possible:

Section Access;
LOAD * INLINE [
ACCESS, USERID, LNK
ADMIN, TOM, 1
USER, JANE, 2
USER, BILL, 3
];
Section Application;

LOAD * INLINE [
LNK, Name, Month, Sales
1, Tom, Jan, 12321
2, Jane, Jan, 31232
3, Bill, Jan, 32131
1, Tom, Feb, 34342
2, Jane, Feb, 34545
3, Bill, Feb, 76575
1, Tom, Mar, 32133
2, Jane, Mar, 12123
3, Bill, Mar, 21321
];

If you need to link on a text field, make sure that you have all capitals - use the UPPER function if necessary.

Seeing the Blindingly Obvious

2010-02-22T21:53:00.003Z

Recently on QlikCommunity, I have proposed a solution however the very brilliant Oleg Troyansky proposed an even better solution: http://community.qlikview.com/forums/p/25381/96915.aspx#96915

Hi answer to the problem (excluding nulls, zero length strings, etc.) was this:

sum({$ < TextField = {"=len(trim(TextField))>0"}>} Qty )

Brilliantly fantastic! Of course, it is even more brilliant if you understand it and, alas, I just couldn't fathom it. It works. It works great. But I didn't see it.

Then my genius colleague, Karol Dorniak, spelt it out for me :- anything that you can type into a search dialog in QlikView can be used in a set! And that includes any "advanced" search syntax!!!

So this is what Oleg was doing - using advanced search. Brilliant.

When you right-click on a List Box in QlikView, there are 3 search options available - Search, Fuzzy Search and Advanced Search. When you click on a list box and just start typing, you get the default search for that List Box (usually the normal search but you can set it to Fuzzy as default on the General tab of the List Box properties). Did you know that you can delete the "**" from the default search and change it to a "~" to start doing a fuzzy search. You can also start typing an "=" followed by an expression (e.g. "=Sum(SalesAmount) > 5000") to do an advanced search - you don't need to go through the advanced search dialog.

So, you can use any of the 3 standard searches by just replacing the search condition with something else ("**", ">", "><", ">=", "~", "=...") Anything that you can type into a search can be used inside a Set!

Man, does this QlikView rock!

More on "Sets =- or -=???"

2010-01-25T16:59:00.003Z

A while ago I blogged about using =- or -= in a set.

I answered a post in QlikCommunity today (http://community.qlikview.com/forums/p/24894/95176.aspx#95176) that demonstrates the use very well.

The question was from sebagiar. He asked how to get an average out of values while ignoring 0s. For example if I have 3 values: 100, 0, 200 then he wants a result of 150, not the 100 that the straight Avg function will give.

My response is to use the following set on the field:

=Avg({< F1 -= {0}>} F1)

So, why "-=" instead of "=-"? Well, the later just tells the field to use all values except 0 - it will exclude any selections on that field. The first one is "additive" (well, I guess I should say "subtractive") and will include and selections on that field and then drop out the zeros.

The right negative

2009-12-22T09:16:00.004Z

I recently had a situation where a customer was not happy with the values returned in a budget variance column.

Simple calculation for variance - (Spend - Budget)/Budget

However, in some of their profit centers there was no budget but there was spend! I.e.: (Spend)/0.

Rather than just display a "-" or 0, they wanted this to display as -100%, to really flag it up.

Following on from my previous post about the RangeMax function in QlikView, I thought about it but then, deciding that wouldn't work for me here, I decided on the Alt function.

The Alt function is quite useful because, like RangeMax, you give it a series of Expressions but what it does is returns you the first one that results in a valid number. So, if I use:

Alt((Spend-Budget)/Budget, -1)

When the variance percentage is Null (when Budget is 0), then the value returned will be -1 (-100%). Exactly what I needed.

Alt can also be useful where you have mixed date formats in a date stream - using multiple Date# functions in an Alt and one of them should give you a number.

Keeping it positive

2009-11-26T10:21:00.002Z

I had a request that when the result of a particular expression was negative, then it should just display as zero (it was a measure of how far out of compliance the result was - a negative result meant that they were in compliance so zero was appropriate).

So I could do a simple:

if(myExpression > 0, myExpression, 0)

That is OK if the expression is simple, but if the expression is more complex then it becomes unwieldy and long and hence difficult to maintain.

A much more elegant way is to use RangeMax like this:

RangeMax(0, myExpression)

Very simple, if my expression dips into the negative then the max value in the range becomes 0. Exactly what I want to achieve and much easier to maintain.

Lies, damned lies...and then there is statistics

2009-11-20T17:19:00.002Z

I spotted this in a Microstrategy publication which showed the "TCO" for Microstrategy over other operators:

This is a fairly typical use of a flawed statistic to prove a point.

You see, if you have a 10 user QlikView system, you have 1 IT person looking after it - the same guy that is looking after Windows, Email, buying coffee, etc. The same guy can probably look after QlikView as the company grows. So that is a 1 IT person for 10. Push that out to 1000 users and it become a "statistic" of 100 IT people per 1000.

Given that, until recently, QlikView has been playing more in the SME sector, then these numbers are going to be more than less typical. Therefore it probably says something that the "statistic" is only 21.9/1000.

At the other end, of course Microstrategy are best because of only 4.9 IT people per 1000. There is no real qualatitive data behind this. Microstrategy play in the Enterprise sector where they will regularly sell large CAL deals as well as open ended deals - we have no idea how many, exactly, users there are.

Its probably good to know that while your 21.9 QlikView IT people are busy looking after the Email server and making the coffee, your 4.9 Microstrategy guys are busy doing Microstrategy and nothing else.

Using Alerts in QlikView

2009-11-20T13:59:00.002Z

Alerts are quite easy to set up. With a few tricks, they can become quite powerful.

Let me start with a very simple data set:

CountrySales:
LOAD * INLINE [
Country, Sales
England, 400
Ireland, 300
Scotland, 200
Wales, 100
];

Now, I want to create an Alert when there are any countries that have Sales > 200.

To create an alert, I need to be able to create an expression that will return a true value. I could use a simple aggr expression like:

=Sum(Aggr(If(Sum(Sales)>1, 1), Country)) > 0

This will work. However, it means that we are going to be limited to what information we can include in the Alert message. We can include the number of Countries, but we couldn't, for example, say which ones they were.

A more powerful way is to combine Advanced Search, Bookmarks and Alerts.

If I create an Advanced Search against the Country field such as:

=Sum(Sales)>200

Then I save this search as a Bookmark. Now, I can create an Alert with this Bookmark used where the Alert is simply:

=Count(Distinct Country) > 0

And the Alert message could be something like:

='Number of countries exceeding 200 = ' & Count(Distinct Country) & chr(13) &
'Countries: ' & Concat(Country, ', ')

Of course, I can bring any other information into this message that is related to the countries that match the Bookmark.

Now, if I set-up the "Mail" options under User Preferences, I can have this document email an alert to someone.

If I turn on the alert to use "Batch Mode", this can happen on the server when the document is reloaded.

AND Mode in list boxes

2009-11-08T18:37:00.006Z

By default, multiple selections in list boxes are "OR" mode. For most applications this is exactly what we need.

However, there are situations, such as that requested by Wolfgang Praschnig on QlikCommunity this week:
http://community.qlikview.com/forums/p/22224/84898.aspx.

Wolfgang wants to be able to see customers who have bought one product AND bought another product. The default action of QlikView would be to show customers who had bought one product or the other.

To enable "AND mode" in a list box, there are a couple of rules that we need to follow:

1. The field in the list box must be contained in a table that has only 2 fields.

2. The other field in the table is the association (or ID) field. This field is only contained in this table.

3. The table must have been loaded with the "DISTINCT" keyword.

This can sometimes be tricky to achieve, so hopefully an example will help demonstrate.

Lets say that we have 3 tables:

Orders
OrderID, CustomerID, OrderDate

OrderDetails
OrderID, LineNo, ProductID, Quantity, Price

Product
ProductID, ProductName

What we want to do is connect the product name to the Orders table in a way that we can employ AND mode.

This means that we will need to create a table that has the OrderID (to associate to Orders) and a ProductName field (which we will call "ProductNameAND") that we can put in our List Box with AND mode on.

So, first we need to create a table with the OrderID and ProductID from the OrderDetails table:

ProductAND:
LOAD DISTINCT
OrderID, ProductID As PID
RESIDENT
OrderDetails;

Note that I have aliased the ProductID field as PID - this is because I am going to drop it in a minute. Also note the DISTINCT keyword.

Now, bring in the Product name:

LEFT JOIN (ProductAND)
LOAD DISTINCT
ProductID As PID, ProductName As ProductNameAND
RESIDENT
Product;

Finally, drop the PID field so we are left with only the 2 fields as required.

DROP FIELD PID;

Now, we can add the ProductNameAND field in a List Box and the AND mode will be available.

Interesting feature here. When you click on an item in an AND mode box, an "&" will appear to the left of it. Pressing CTRL and clicking on additional "possible" (in white) items will add further "&"s beside those items. However, if you click on an "excluded" item or you click and hold the mouse down for a second on "possible" items, it turns to a "!" instead - i.e. "NOT". This can be really powerful for queries.

SR2 released and looking good

2009-11-08T18:25:00.002Z

Well SR2 for QlikView 9 was released last week.

So far, so good. Looks like all my reported bugs have been fixed.

My opinion is that this Service Release is really what SR1 should have been if it hadn't been rushed forward to get some of the bad bugs from the initial release fixed.

With all of the great things that are in v9, we can now get on with the business of implementing it freely without having to worry about bugs that just shouldn't have been there.

Hurry over to QlikCommunity - http://community.qlikview.com - to get your copy.

Retrieving Database Values from within a document

2009-10-06T15:39:00.002+01:00

There may be many reasons why you might want to get a value from outside of QlikView at any time, without having to reload your document (even partially).

The obvious method is to use macros to retrieve the data using ADO calls.

However, there is a function in QlikView that can do this - but only to retrieve one value (e.g. a record count). The function is called SqlValue.

The function takes 3 parameters:

- Connection
- Query
- Connection Type

Connection Type is only one of 'OLEDB' or 'ODBC'. If you leave it out, ODBC is assumed.

The Query is a string that should (although not absolutely required) return one row and one value. If it returns more, only the first value in the first row is returned.

The Connection is straightforward if using ODBC because you just use the predefined ODBC connection name. For OLEDB, you need to pass an OLEDB connection string. Now, this can be messy (as they can get quite long and include single quotes which can throw you a bit. For me, the easiest way is to set up a variable with the Connection string in it (perhaps it could be stored in the registry and retrieved using GetRegistryString!)

Once you have it, then you just call like this:

=Num(SqlValue(MyDatasource, 'SELECT Distinct Count(*) As MyCount From Orders', 'OLEDB'))

Note that I have used the Num function here to cast the return value as a numeric. The SqlValue function returns a dual so, if you want to display it, you need to cast as numeric or it will display as blank (no Text value in the Dual).

The query that you pass can be as complex as the target database supports and can use values from your QlikView document:

=Num(SqlValue(MyDatasource, 'SELECT Distinct Count(*) As MyCount From Orders Where CustomerID in (' & Concat(Distinct CustomerID, ',') & ')', 'OLEDB'))

Obviously, and this is flagged in the help file, if you have queries running in your document, they can slow things down while waiting for the database query to return.

Removing Fields with a Wildcard

2009-10-06T14:36:00.002+01:00

One of my clients asked an interesting question. They will have a standard set of QVDs to load data from that may be used in various documents. In each document, they will also have a mapping table to mass rename the fields that they need for that particular document.

Now, with their dataset, they will be left with a load of fields that start with the word "Custom_" - sometimes some of these fields will be aliased, sometimes they will not be used. If they are not used, then they will want to drop the fields from the document (to keep things nice).

Unfortunately, QlikView does not accept a command like

DROP FIELDS "Custom*";

So, we can use a couple of functions that are available in the script to do this for us. Here is a script sample:

MyTable:
LOAD * INLINE [
F1, F2, F3, F4
1, 2, 3, 4
];

Rename Field F1 to NewField;
Rename Field F3 to NewField2;

Let i = 1;

Do While i <= NoOfFields('MyTable')

Trace Getting Field $(i) From MyTable;

Let FieldName = FieldName($(i), 'MyTable');

Trace FieldName = $(FieldName);

Let Command = If('$(FieldName)' Like 'F*', 'Drop Field $(FieldName);', '');

Trace Command = $(Command);

$(Command)

Let i = $(i) + If('$(Command)' = '', 1, 0);

Loop

So, I load 4 fields, rename 2 of them, then loop through all the fields looking for a match ('F*') for the original names and create a command to execute in the script. (This is also a good example of using a dynamically created command in a script!)

The reload window should look like this:

MyTable << INL47EA 1 lines fetched
Getting Field 1 From MyTable
FieldName = NewField
Command =
Getting Field 2 From MyTable
FieldName = F2
Command = Drop Field F2
Getting Field 2 From MyTable
FieldName = NewField2
Command =
Getting Field 3 From MyTable
FieldName = F4
Command = Drop Field F4

Note that you need to use a While statement here not a For loop. This is because once you drop a field, say Field #2, the next field becomes that field number - Field #3 becomes Field #2 - so you will end up skipping fields. The While statement prevents this.

Using Sets to avoid Key Tables

2009-09-17T17:37:00.006+01:00

This may not appeal to purists, but it might be a good way to get out of a fix when you are in a hurry to get something in front of people.

Let's create a QlikView Script:

Sales:
Load * INLINE [
Date, Item, Store, Quantity, Price, NetValue
1/1/2009, 1, 1, 1, 2.00, 2.00
1/1/2009, 1, 2, 2, 2.00, 4.00
1/1/2009, 2, 1, 1, 2.00, 2.00
1/2/2009, 2, 2, 1, 3.00, 3.00
1/2/2009, 1, 1, 3, 3.00, 9.00
1/2/2009, 2, 2, 1, 3.00, 3.00
];

Product:
Load * INLINE [
Item, Description
1, Product A
2, Product B
];

Store:
Load * INLINE [
Store, Name
1, Store 1A
2, Store 2B
];

Now, this will create a nicely connected QlikView document. Now, I am going to introduce a Stock table that tells us the stock levels for the products in each store:

Stock:
Load * INLINE [
Store, Item, OnHand
1, 1, 55
1, 2, 33
2, 1, 23
2, 2, 12
];

This will create a nice Synthetic Key!

Now, the normal approach to avoiding the Synthetic Key is to start creating a Key table to nicely join all the tables. This, of course, uses lots of Joins and Concatenation and Resident loads (which are slow!!!). All of this means that your document loading takes a lot longer - losing any QVD optimisation that you might have.

Here is my solution to resolve this Synthetic Key:

Stock:
Load * INLINE [
StockStore, Item, OnHand
1, 1, 55
1, 2, 33
2, 1, 23
2, 2, 12
];

"Hey! You can't do that", I hear you all shouting. You have broken your links! How can you see the correct stock levels? If someone selects a Store, then the value of the stock in the chart won't change! If you chart it now, you will just get the same value because there is no association.

And this, of course, is true. If I, for example, create a Straight Table with a dimension of "Description" (the Product Name) and use the expression:

Sum(OnHand)

I will get the 78 for Product A and 45 for Product B. Now, if I select Store 1A, the values will not change - which is incorrect.

But how about if I use this expression:

Sum({<StockStore = P({$} Store)>} OnHand)

Now the values do change if I make selections on Store.

This is using the new "P" option in Set Analysis to allow me to make a set of the relative values. So, my "P" returns a set of the selected Stores in this document and then I am using that to filter the StockStore value.

One downside - and I would love to hear from someone who can overcome it - is that it doesn't work if the dimension is Store instead of Product. I need to work on that.

Microsoft Gemini

2009-09-17T00:23:00.003+01:00

I just rewatched this video again: http://www.youtube.com/watch?v=MzgMMO-P9F0 - A preview of Microsoft Gemini in Excel 2010.

Why is it that when he says, 'we call them "Slicers"', I can hear Dr. Evil saying 'I call it a "LASER"' ;-)

Well, Gemini is now available as a Community Preview. You can get access to the Gemini CTP if you are a member of the Office 2010 Tech Preview by visiting http://www.microsoft.com/officebusiness/office2010/Default.aspx?vid=Gemini (if you go to that link, click on "Videos", there is a really bad "Project Gemini for Excel 2010" video that you can watch).

Is Gemini a "QlikView Killer"? I am not sure. All I can say is that my experience tells me that QlikView rocks compared to this demo. My experience also tells me that Microsoft are into marathon's, not sprints.

Avoid Resident for large data sets

2009-09-16T15:00:00.002+01:00

As part of your training, you were probably taught to us Resident loads to re-use data that has already been loaded. I have used this and taught this for several years.

However, my experience recently with larger data sets is that resident load is very slow!

A much better choice is to re-use a QVD if you have one. In fact, if you write the in memory table to a QVD (using STORE), and then use that QVD for your next load, it is still faster than using Resident!

Custom popup labels on bar chart

2009-09-16T13:26:00.004+01:00

The default pop-up for a QlikView bar chart is not always the most friendly. It shows the values for dimensions and expressions in the form:

  Dimension 1 = Value
  Dimension 2 = Value
  Expression = Value

Now, we may not like this popup and wish to display the values differently or add different formats. We may even want to include additional information.

Well, you can. There are a couple of steps:

Step 1
For your existing expression, make sure that the "Text as Popup" option is turned off.

Step 2
Add a new Expression that calculates out the correct string that you want to display in your popop with all the correct formatting. For this expression, turn on that "Text as popup" option. But we don't want the chart trying to display a real bar for this expression so take off the "Bar" option (the icon for the expression will change from the Bar icon to a text bubble icon).

Step 3
We need to stop the default popup - on the Presentation tab, turn off the "Pop-up labels" option.

Job done.

If you are on QlikCommunity (and if you aren't, why on earth not???) then you can grab a demo of this created by me from:
http://community.qlikview.com/media/p/78614.aspx

More on Dual

2009-09-14T12:10:00.003+01:00

I posted recently about the very useful Dual function (to store a numeric value and a text value - e.g. for Months).

Another good application of this function is in a chart calculation. If your calculation includes a dual, e.g.:

Dual(Country & ' (' & Num(Sum(Sales)/Sum(Total Sales),'0.00%') & ')', Sum(Sales))

Then you will have both a Number and a Text value - either or both can be displayed on the chart using the "Text on Datapoint" and/or the "Numbers on Datapoint" options in the Express Tab of the chart.

One use of this is to display 2 disfferent calculations on, say, a pie chart. In the above example, the Pie chart could be set to display both the total values and the percentages - something that is not achievable any other way.

Keeping a Trace on things

2009-09-10T13:34:00.002+01:00

I love QlikView. I love the way that I find new things all the time.

Occasionally, when I am very bored, I sometimes have a look in the help file for things that I am not familiar with. That is how I learned about Trace.

Trace is very cool in that it allows you to both comment your code and have those comments appear in the Script Execution dialog. It also supports variable use.

So, I could have a script like this:

For i = 1 to 10

Info:
Load
Information
From File$(i).qvd (QVD);

Next

In the past, I may not have been exactly sure where in the look I am without having to physically count the entries in the Script Execution dialog. Now, I can add a trace into the loop:

Trace Loading from file: File$(i).qvd;

And it will appear in the Script Execution like:

Loading from file: File1.qvd

What's more, it also appears in the script execution log file (you have to turn that on in the Document Properties - General - Generate Log File).

I wonder what interesting things that you might find in the help file?

Using Dual

2009-08-29T15:46:00.002+01:00

QlikView has a really cool data type called "Dual". This type allows you to mix text and numeric values which can be very useful.

For example, imagine that you had some pieces of text, say:

Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep

And you would prefer to have them sorted in another order, say:

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

Then we could load this something like:

Load
Dual(Name, Num) As Month;
LOAD * INLINE [
Num, Name
1, Jan
2, Feb
3, Mar
4, Apr
5, May
6, Jun
7, Jul
8, Aug
9, Sep
10, Oct
11, Nov
12, Dec
];

And the Month field would be sorted in the correct order.

If we need to get the component values spearately out of a Dual value, we can use Num and Text:

Text(Month) & ' (' & Num(Month) & ')'

Might give us a result of:

Apr (4)

Change the Command Centre port in v8.5

2009-08-25T14:42:00.006+01:00

Thought that I might document this here while I still have an 8.5 server to play with.

Often, other applications will grab the ports that QVP processes use on your server (e.g. McAfee AV will often use 8081). To change the QVP ports there are a couple of places that you need to make changes.

In Program Files\Qlikview\ControlPanel, edit web.config and change the entry:

< add key="CommandCenterURL" value="http://localhost:8081/qtcc.asmx"/ >

to

< add key="CommandCenterURL" value="http://localhost:8765/qtcc.asmx"/ >

(I am using 8765 as the example port to change to).

In Program Files\Qlikview\CommandCenterService, edit QVPublisherCommandCenterService.exe.config and change the entry

< add key="WebservicePort" value="8081" / >

to

< add key="WebservicePort" value="8765" / >

Stop and start the services and you should be OK. You can always test the service on the server by calling the web service:

http://localhost:8765/qtcc.asmx

It should respond:

CommandCenterService v 2.0.50727.3082 says, "Hello world. The time is: 8/25/2009 14:39:39.8992663". For wsdl, add ?WSDL at the end of the request.

There are similar processes to change and/or test the other services:

Execution Service: http://localhost:8082/qtxs.asmx

or http://localhost:8082/debug

Directory Service: http://localhost:8083/qtds.asmx

32bit ODBC/OLEDB on 64bit Server

2009-08-20T12:08:00.005+01:00

This is something that I see questions about all the time. The quick answer is that QlikView 64bit cannot use a driver that is compiled for 32bits.

Why?

If I run an application on a 64bit server in 32bit mode, it has access to all the 32bit OLEDB/ODBC drivers. For example, I have a VBScript that generates OLEDB connection strings. If I run this using the %sysdir%\SysWOW64\csript.exe (the 32bit version of vbscript), I get the following list of OLEDB drivers:

I can see that the Microsoft Jet driver (for Access) is available.

However, when I run my VBScript application in 64bit mode, I get a different list:

No Access driver anymore!

The Microsoft Access drivers are old and have never been compiled for 64bits. This means that a 64bit application running on a Windows 64bit server cannot see them or use them.

QlikView 64bit is not a 32bit application that just runs on a 64bit server, it is an application that is fully compiled to operate in 64bits. I doesn't even know that the Access driver exists because Windows doesn't tell it about it.

So, the limitation is not to do with QlikView, it is a limitation of the company who have delivered the driver.

Luckily, most large database companies (Oracle 10g+, Teradata, Microsoft, etc.) have either OLEDB or ODBC drivers that work in 64bits.

If you just can't get your hands on a 32bit driver? Well, you could go down the route of installing 32bit QlikView client on your server (or another server?) and use that in Command Line mode to access the 32bit driver, read the data and store into QVDs that your 64bit publisher can read. This will of course consume one of your CALs or a developer license, so it is not idea.

The ideal solution is to see what you can do to get a 64bit version of the driver.

Google Maps QVW broken

2009-08-19T12:14:00.004+01:00

The Google Maps example that comes with QlikView 9 has stopped working.

This is because Google have made a couple of changes. Luckily, it is resolveable.

Step 1:

Go to http://code.google.com/apis/maps/signup.html and sign up for a Google Maps API key.

This key, once you receive it, should be put into the document variable, gmap_key (the default value of "xx" will no longer work).

Step 2:

The .jpg that we need to put at the end of the URL so that QlikView knows the result is an image will break the URL because it is currently tagged onto the end of the API key. We need to add an additional '&' before the '.jpg' so that Google Maps can interpret the values correctly. It will ignore the "&.jpg" at the end.

- this appears to work OK (in the Colors tab of the Chart properies - Dynamic Image):

='http://maps.google.com/staticmap?center='
&
num(var_mid_lat, '##############', '.', ',' )
&
','
&
num(var_mid_long, '##############', '.', ',' )
&
'&zoom=$(var_zoom)'
&
'&size='&map_size_x&'x'&map_size_y
&
'&key='&gmap_key
&
'&maptype='&var_maptype
& '&.jpg'

Working fine for me now.

Very Useful YearToDate

2009-08-18T10:29:00.002+01:00

Many people will have used YearToDate (or Year2Date) to calculate out whether the current date is Year to date for this year, last year, etc.:

YearToDate(date_value)

or

YearToDate(date_value, -1)

But there are some other values of YearToDate that make it very useful. The full syntax is:

YearToDate( date [ , yearoffset [ , firstmonth [ , todaydate] ] ] )

The third parameter is very useful because it allows us to use the one function for MonthToDate and QuarterToDate to compare Year-on-Year or dynamically change between YTD, MTD and QTD (there are functions InMonthToDate and InQuarterToDate that could be used but they would require different Year offset calculations and are less easy to make dynamic use of)

For example:

YearToDate(date_value, 0, Month(Today()))

gives us Month to date this year.

YearToDate(date_value, -1, Month(Today()))

gives us Month to date last year.

With a handy use of the Ceil function, we can also calculate Quarter to date:

Ceil(Month(Today())/3) * 3 - 2

This value will always return the first month of the quarter containing the Month number passed (in this case, Month(Today()) )

So, plugging this into our YearToDate:

YearToDate(date_value, 0, Ceil(Month(Today())/3) * 3 - 2 )

gives us Quarter to date.

YearToDate(date_value, -1, Ceil(Month(Today())/3) * 3 - 2 )

gives us Quarter to date last year.

Use of appropriate variables means that we can give the user a very useful dynamic swap between YTD, QTD and MTD - if that is required.

"=" sign in the expression builder

2009-08-18T10:07:00.002+01:00

There is a very simple explanation for using the "=" sign in the expression builder - it tells QlikView to evaluate everything after the "=" and return the result.

Where there is some confusion is that within charts (and, in successive versions it has been removed in other places) the "=" is not necessary because it is assumed - you wouldn't put an expression in a chart unless you wanted it to be evaluated, would you?

In a text box, the need for the "=" becomes more apparent. If I add a Text Box to my layout and set the text expression to:

Sum(Sales)

Then the Text Box will come to the screen and have a value of "Sum(Sales)". This makes sense because the default interpretation of a Text Box should be that the values in the expression are just text. Now, if I add the "=" into the expression:

=Sum(Sales)

Then the calculation is performed and the result, the sum of all sales based on current selections in the document, is displayed in the Text Box.

It becomes more interesting when we use, or do not use, the "=" in a variable.

For example, if I set up 2 variables, vSales and vTotalSales.

vSales is going to have a value set up in the Expression Builder of:

Sum(Sales)

vTotalSales is going to have a value set up in the Builder of:

=Sum(Sales)

Now, if I set up a straight table and add a dimension, say "Year", and then add 2 expressions - one that is just $(vSales) and the other is $(vTotalSales), then the first column will show sales by Year and the second will show the same value in each row - the total sales for the document.

This happens because the first variable is simply a piece of text - "Sum(Sales)" - that is replaced into the chart expression and evaluated against each dimension. The "=" in the second variable causes it to be evaluated at the document level and it is the result rather than the expression that is inserted into the chart.

The "=" is also interesting in Dollar Expansion. We could achieve the same result as vTotalSales in the chart by using the expression:

$(=Sum(Sales))

This Sum(Sales) is now calculated outside the chart - i.e. at the document level - and the result, not the expression, is returned to the chart.

Knowing about the "=" sign in Expression Builder is very useful to avoid confusion and also to give you a new weapon in your arsenal.

Section Access Gotcha's

2009-08-14T14:23:00.002+01:00

Couple of things that can cause you problems in Section Access:

- Loading Null values into the USERID field. You won't get back into your document!

- Loading mixed/lower case values into USERID and PASSWORD. Although they are not case sensitive for users entering them, they must be loaded in CAPITALS.

Sets =- or -=???

2009-08-10T16:06:00.005+01:00

This question arose recently:

Note: Using gt and lt for Greater Than and Less Than symbols - sorry.

What is the difference between the set { lt Value = -{"X"} gt } and the set { lt Value -= {"X"} gt }?

Well the difference is that the first set is a modification of the $ set and saying that I want all values of the "Value" field excluding the value "X" (it could have also been written as { lt Value = {*}-{"X"} gt ).

The second set is different in that it is additive (or actually subtractive) - it will take whatever selections are on the "Value" field and take off the "X".

So, for the first one, if you make any selections on the "Value" field, they will be ignored. For the second one, your selections will be reflected in the result except if "X" is part of your selections it will be excluded.

As an example, take the following dataset:

Sales:
Load * Inline [
User, Sales
John, 12222
Tom, 11000
Jane, 19000
May, 21000
Graham, 13300
];

If I create a straight table with User as the dimension and the following 2 expressions:

Sum({ lt User = -{"John"} gt Sales)

Sum({ lt User -= {"John"} gt Sales)

Then whatever selection I make on User will not effect the result - the chart will list the sales for Time, Jane, May and Graham (no John). For the second, if I make selections on a user, the other users will zero out but I will still see no John.

Enjoy.

Days360

2009-08-10T12:41:00.001+01:00

Recently a client asked me about the Excel function Days360. This is a commonly used function within the financial industry to calculate the number of days between two dates based on a 360 day year - i.e. 12 x 30day months.

The function in Excel takes 2 mandatory parameters and 1 optional parameter:

DAYS360(start_date,end_date,method)

If the "method" parameter is FALSE or left out, then it uses the US (NASD) method. If it is TRUE then it uses the European method. The difference is that in the European method if either the start or end date are on the 31st of the month, they are considered to be on the 30th of the month.

I have a simple VBScript function that replicates this functionality:

Function Days360(StartDate, EndDate, European)

Dim Days1, Days2
Days1 = Day(EndDate)
Days2 = Day(StartDate)

If European and Days1 = 31 Then Days1 = 30
If European and Days2 = 31 Then Days2 = 30

Days360 = DATEDIFF("m", StartDate, EndDate) * 30 + Days1 - Days2

End Function

Unfortunately VBScript doesn't have a MIN function or this could be reduced to be even simpler.

Now, if I define ths VBScript function in my QV Document, I can call it from within the load script in a QlikView application. Since version 8.2 though, I am unable to call macro functions from the chart so I have to have a different solution.

Oleg Troyansky gave a good formula for the month difference: http://community.qlikview.com/forums/p/9923/39894.aspx#39894

MonthDiff = (year(Date1)*12 + Month(Date1)) - (year(Date2)*12 + Month(Date2))

This works great for me. So, I now just need to add the days subtraction. This needs to be two separate functions, one for US and one for Europe.

US:

=(((YEAR(Date2)*12 + MONTH(Date2)) - (YEAR(Date1)*12 + MONTH(Date1))) * 30) + DAY(Date2) - DAY(Date1)

Europe (using the Nummin function to step back a 31st to 30th):

=(((YEAR(Date2)*12 + MONTH(Date2)) - (YEAR(Date1)*12 + MONTH(Date1))) * 30) + Nummin(DAY(Date2),30) - Nummin(DAY(Date1),30)

I hope that this helps others get past this one.

It is worth remembering. No matter what function that you come across that doesn't exist in QlikView, it has to be calcuable some way - and probably is do-able within QlikView.

Does it exist?

2009-08-10T12:18:00.002+01:00

In QlikView, we have the option to check if a value is already in a field using the "Exists" clause. I have seen some confusion with it over time, so hopefully this will help.

Exists is an inter-record function that we can use within the load script to check for the existence of a value in a previously loaded field.

The syntax is as follows:

exists(field [, expression])

The field is a field that already exists in our document - which includes any fields in the current load statement.

The expression is a calculation based on the data that we are currently loading.

Note that expression is not a required field. If it is excluded, exists calculates if the value of the field exists in any previous loaded value for that field.

Here is a simple example of using Exists to only load data that exists in a previously loaded field.

Budget:
Load * Inline [
User, Budget
Tom, 10000
Jane, 20000
May, 22000
Graham, 12300
];

Sales:
Load * Inline [
User, Sales
Tom, 11000
Jane, 19000
May, 21000
Graham, 13300
John, 23456
]
Where Exists(User);

The first load statement loads budget information for users Tom, Jane, May and Graham. The second load statement brings in the sales values for users but we state that we only want values for users already exist.

In this case, no values will be loaded for John.

Here, we didn't need to pass an expression (although we could have done Exists(User,User)) because we were looking for a value in the same field that was being loaded.

Here is another example of a similar sales loader:

Sales:
Load
RowNo() As row,
User,
Sales,
If(Exists(User,User), 1, 0) As Exists
;
Load * Inline [
User, Sales
John, 12222
Tom, 11000
Jane, 19000
May, 21000
Graham, 13300
John, 23456
];

If we now put a table of values for row, user and exists, we would see that John in Row 1 has a 0 for exists and in row 6 he has a 1. This shows us that Exists is working on data that is loading in the current load statement also, not just on data that has been loaded before.

The Expression parameter of the Exists statement is very useful - especially if you have complex keys in an Load.

Qlik Tips

Saving Space by Nulling Zeros

The Right Background

Easy Ad-Hoc Analysis in QlikView

Delivering BI to Customers

Denormalize for Performance

Color coding RAGs

Setting default display format

Setting default sort order

Irish crime stats. Sleeping easy in my bed?

Out of Control? Dublin Climate Data

Is it time to replace the Quick Chart Wizard?

Alternative to Grid-Style Multi-Boxes in Ajax

Nobody said it was easy ... Configuring LDAP

AND Mode in Set Analysis

It's the discovery, stupid!

Rules for creating a Key/Link Table in QlikView

Part-to-Whole Time Series

Alternative to Null

Better BI Dashboards

Improve load performance by writing better script!

Blocking user access to a field using OMIT

Defending Pie Charts!

Data for Humans

Best practice on iPad user interface design

Your call...Qlikview Server and AccessPoint

Your call...

Load testing QlikView with JMeter

QlikView Mobile

No printing in Ajax

Beginners Guide to QlikView Extension Objects : Part 4 - Headers

Parameters in Dollar Expansion

Beginners Guide to QlikView Extension Objects : Part 3

Beginners Guide to QlikView Extension Objects : Part 2

Beginners Guide to QlikView Extension Objects : Part 1

Things I like about QlikView 10 - Part 3: Extension Objects

Things I like about QlikView 10 - Part 2: UI

Things I like about QlikView 10 - Part 1: Script

Do we really need maps for analysis?

It is often simpler than it looks

Access DBs on 64bit

Installation ended prematurely because of an error

More on Dual - "Others" in chart using AGGR

Use a ramdrive!

Beta testing QlikView 10

How QlikView stores data

Radish Bits

Watch your Security Connections

Seeing the Blindingly Obvious

More on Dual - Problem in Sets

More on "Sets =- or -=???"

The right negative

Keeping it positive

Lies, damned lies...and then there is statistics

Using Alerts in QlikView

AND Mode in list boxes

SR2 released and looking good

Retrieving Database Values from within a document

Removing Fields with a Wildcard

Using Sets to avoid Key Tables

Microsoft Gemini

Avoid Resident for large data sets

Custom popup labels on bar chart

More on Dual

Keeping a Trace on things

Using Dual

Change the Command Centre port in v8.5

32bit ODBC/OLEDB on 64bit Server

Google Maps QVW broken

Very Useful YearToDate

"=" sign in the expression builder

Section Access Gotcha's

Sets =- or -=???

Days360

Does it exist?