Monday 30 April 2012

QlikView Scripts in Notepad++

NOTE: Links in this post are to deprecated versions, please see the QlikView Notepad++ page for the latest version and instructions.

I've been working on a project for the last few weeks which has required me to use a lot of separate QlikView script files. For those that don't know, it is possible to include text files containing QlikView statements into the script of a QVW using the following statement:

$(Include=abc.qvs);

The lines in the separate file will be included in the QVW script at the point of the include statement and run as if they were in the QVW script. Note that the file extension is not important but .qvs is the extension QlikView uses when you ask it to export the script from a QVW so I generally stick to using the same. Using separate script files can be a really powerful tool when trying to perform the same data loading and manipulation tasks in multiple apps. Changes can be made to the script file once rather than having to make the same change repeatedly in the multiple applications where its used.

Anyway, on to the real point of this post. I like to use Notepad++ as my text editor when working with XML, HTML, etc and so naturally that's where I went to create my QlikView script files. But I quickly became annoyed that the nice QlikView syntax highlighting that I'm used to when working in QlikView Desktop was missing, making reading and editing the script much harder. So with a spare half an hour I created a Notepad++ custom language definition for QlikView and thought I'd share it so others can benefit too. Its by no means perfect and won't give you an exact representation of the script as you would get in QlikView's Edit Script dialog but it will highlight comments, functions and most keywords. It supports the following:
  • All current QlikView functions
  • Most commonly used keywords
  • Line comments (//) and block comments (/* */)
If you find any keywords missing then leave a comment below and I'll add them and re-release. I've also included support for a little something I've been working on and am hoping to release soon. Watch this space!

Installation

Here are the steps to getting it installed and working yourself:
  1. If you don't already have it, you can download Notepad++ from http://notepad-plus-plus.org/download/. Its completely free and a great text editor. If you are running an older version I'd recommend you upgrade to the latest as some older versions can't import language definition files and so you'll get stuck with the next steps.
  2. Download the QlikView language definition XML file from http://qvacb.googlecode.com/files/qlikview-lang-def-v0.3.xml.
  3. Open Notepad++ and navigate the menu to View-> User-Defined Dialog...
  4. Either a floating window or a panel to the right will appear, click on the "Import..." button within it.
  5. Navigate to the location where you saved the XML file to and select it and click "OK".
  6. Hide the language panel by again selecting View-> User-Defined Dialog... on the menu.
If you open a .qvs file, Notepad++ will automatically identify it as a QlikView script file and apply the language definition for you. If you are creating a new file or using a different file extension then you'll need to change the language using the menu  Language -> QlikView.

And that's it! Happy QlikView scripting.

Thursday 19 April 2012

It wasn't me officer!

I received a message this week asking if I might be the infamous "QlikView addict" from the well known video released back in 2006. Alas, I can't take credit for this but I thought I'd post it for those who have never seen it. If you can't remember the days of QV 7 & 8 you might not get all the jokes but it's still amusing to watch.


Thursday 12 April 2012

QlikView 11 SR1 Re-released

QlikTech have re-released QlikView 11 Service Release 1 today after withdrawing it because of a significant bug. It's available from the download site as usual.

Wednesday 4 April 2012

Handling Nulls in QlikView

The way in which QlikView's associative engine works means Nulls don't really exist in the data it holds, a Null is simply an absence of a data item. This can make working with Nulls a little tricky until you understand the tricks and tips which can make it a little simpler. For example, what if I want to display customers with missing phone numbers, I can't simply make a selection for Null in the phone number list box as it doesn't exist as a data item.

Here are a few tips and tricks which can make working with Nulls in QlikView a little simpler.

The Null() and IsNull() Functions

It is possible to generate a Null value in the script or an expression using the null() function. For example, you might want to replace zero's with a Null so that they don't take up valuable resources when dealing with very large data sets or don't effect an average calculation. This is pretty simple using the null() function like this:

if(SalesValue=0, null(), SalesValue) AS SalesValue

With this in mind you might think you could also use the null() function to test if a field contains Null like this:

if(SalesValue=null(), 'NULL', SalesValue) AS SalesVale

Great, I can now select the Nulls right? Wrong! It is not possible to compare against the null() function as shown above and interesting doing so will not result in a script error either. Instead a separate isnull() function exists for this purpose and can be used like this:

if(isnull(SalesValue), 'NULL', SalesValue) AS SalesValue

The NullAsValue Statement

The above example of replacing Nulls is often the first solution people reach for when building a QV app. However there is an alternative which can not only be cleaner but also a more efficient solution. The NullAsValue statement does as you might expect it to, replacing Nulls in the given list of fields with a value which can be seen and selected in a list box. The syntax is very simple as follows:

NullAsValue Field1, Field2;

As you can see, it is possible to list more than one field and it will also except wildcards in a similar way to the Qualify statement as described in a previous post here. By default, NullAsValue replaces the Null with a zero length string but this can be changed by setting the NullValue variable as follows:

SET NullValue = '<Null>';
NullAsValue SalesValue;
NullAsNull *;


There is one thing to be careful of when using NullAsValue though. Optimised QVD loads do not reprocess the data within the QVD row by row and thus it will not apply the NullValue to the data. To get around this you must force an unoptimised load of the QVD. This can be done by adding a WHERE clause to the load that will always apply like this:

LOAD
    *
FROM myqvdfile.qvd (qvd)
WHERE 1=1;

Nulls in Basic Maths

Another issue with Nulls occurs when you want try to perform simple maths with them. For example, you might expect that 5 plus Null is 5 when the real answer is in fact Null. This can be a very real problem when adding 2 fields together and one or both have records containing Nulls. As always there are multiple solutions.

One method is to use the alt() function which accepts a list of values and returns the first which is a true numerical value. We can thus use it as follows to solve the problem of performing maths around Nulls:

alt(SalesValue,0) + alt(SalesMargin,0) AS GrossSalesValue

If either SalesValue or SalesMargin contain a Null value, it is instead treated as zero and the calculation will give the result we might have expected instead.

An alternative is to use the range functions such as rangesum(), rangecount() and rangeavg(). These functions are passed one or more values and return the sum, count or average respectively of only those passed values that are truly numerical. Nulls are not considered numerical values and thus are ignored and so we can can use rangesum() to solve the same addition problem as follows:

rangesum(SalesValue, SalesMargin) AS GrossSalesValue

Counting Nulls

It can often be necessary to count Nulls. For example I might want to know find out how many customers I have no address for. Counting Nulls in QlikView is made easy using the nullcount() function which pretty much does exactly as you'd expect. It can be used in both the script and expressions and the syntax is very straightforward:

=nullcount(Address)

Selecting Nulls (including in set analysis)

You obviously can't directly select Null in a list box as it won't appear although you could use one of the methods describe above, such as NullAsValue, to convert the Nulls into a value that can be selected. You might think searching for "=null()" might work bit it won't. However it is possible to indirectly select Nulls within list boxes or even set analysis expressions in QlikView using an advanced search. You can't search within the same field as it won't possible to select the Nulls when you apply the search but you can search in one field for those values that relate to where another field is Null.

Using the example we considered above of finding my customers for whom I have no address, we can't search for null() in the Address field but we can search the Customer field for isnull(Address) like this:


Advanced searches can be used in set analysis expressions as well as list boxes and so we could use the following expression in a chart or text object too:

count({$<Customer={"=isnull(Address)"}>} Customer)

I'll leave you with one last thing to keep in mind with Nulls and set analysis. One thing that sometimes people don't fully understand is that there is a big difference between selecting all values in a field and selecting no value. The difference occurs because of Nulls. Selecting all values in a field excludes records for which that field contains a Null value. You can do just this in set analysis like this:

count({$<Address={"*"}>} Customer)

And a quick word of warning. You may see the following syntax to exclude Nulls written sometimes on blogs and forums:

count({$<Address-={"null()"}>} Customer)

If you try it you'll find it may well work, but it is considered bad syntax and QlikTech may correct it in the future and so it is best avoided to ensure your application is future proof.

Monday 2 April 2012

QlikView 11 SR1 Update

For those that watch my Twitter feed, you'll have seen me "tweet" last week that QlikTech had withdrawn QlikView 11 SR1 from the download site because of a bug that had been found. The bug meant that any changes to the document settings aren't saved and although this does not pose a security risk, they have decided it is serious enough to warrant withdrawing the build.

On Friday last week they released the below announcement on their blog site:
QlikTech has withdrawn Service Release 1 of QlikView 11 from the download site due to issues discovered with the release. These issues do not impact the initial, generally available QlikView 11 release.

Given the nature of the problems discovered with Service Release 1, QlikTech has decided to rerun a complete internal testing and validation program prior to reissuing Service Release 1 to ensure the highest possible quality standards are met. This process is underway and we currently estimate that Service Release 1 will be re-issued in mid-April. We apologize for any inconvenience caused by this change and we encourage customers who downloaded Service Release 1 to hold off testing or deploying this release until the update becomes available.

Again, these issues do not impact the initial release of QlikView 11, currently available on the download site.

Any further updates related to Service Release 1 will be posted on this site.
So it seems we'll just have to wait a week or 2 longer to get a new release of SR1.