Use a ramdrive!

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

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

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

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

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

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 Dual - Problem in Sets

I have discussed using Dual a couple of times in the past. It can be enormously useful. Sometimes it can throw you a bit - especially if you do not realise that you are using a Dual.

An example comes up in my response to the post of jhoffmann on QlikCommunity.

He was asking about doing calculations in a Set. This is, of course, eminently achievable using the dollar-expansion syntax. You do have to watch it when using a Dual value.

He was using a field called "RollingMonth". I assume, because it comes from the (old) training manual, that this was created in the Script using:

...
Date(MonthStart(date_field), 'MM/YY') As RollingMonth,
...


This syntax creates a Dual value - with the month's date (as an integer) and the text as per the format string.

This is quite fine in a Set if you use a simple calculation such as Max:

Sum({<RollingMonth = {"$(=Max(RollingMonth))"}>} Amount)

You see, Max knows about Dual and will return the correct value so that the comparison will work - it returns the Text value. It is the text value that is required in the Set comparison. If the max month is January 2010, Max will correctly return (in this case) "01/10" - even though it is alphabetically less than the "12/09" before it. Max does the Dual sort on the number and returns the text. Simple.

The problem arises with some other functions that are not so "Dual aware". For example, if we wanted to see the values from the same month last year, we might try this:

Sum({<RollingMonth = {"$(=AddMonths(Max(RollingMonth),-12))"}>} Amount)

The problem is that AddMonths takes a date value as a parameter and returns a date. It doesn't care about the text value. It uses the Dual's numeric value to do the calculation and then returns a date/numeric value. For example, it might be "01/01/2009". This will not work in our Set above (RollingMonth={"01/01/2009"}) because the text doesn't match and the Sum will return 0.

To fix this one, we just need to re-establish the Dual value:

Sum({<RollingMonth = {"$(=Date(AddMonths(Max(RollingMonth),-12),'MM/YY'))"}>} Amount)

Now the calculations will work perfectly.