Using Sets to avoid Key Tables

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

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

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

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

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

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?