It's the discovery, stupid!

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

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

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

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