Monday 17 December 2012

QlikView for Notepad++ Release 1.1

As I mentioned in my previous post, Notepad++ have released a new version of their language definition engine which supports several new features. Because of this, I have updated the Notepad++ QlikView language definition file to support these features where possible.

The new language engine is found in Notepad++ versions 6.2 and greater. If you are running a previous version you will need to upgrade to the latest before installing the new language definition file. Full instructions and links can be found here http://www.qlikviewaddict.com/p/qlikview-notepad.html.

The new features are as follows:
  • Ability to collapse blocks of comments, both multiple line comments (//) and block comments (/* */)
  • Ability to collapse blocks of code including IF, FOR, DO and SUB statements
As always, if you find any issued leave me a comment below.

Thursday 13 December 2012

Notepad++ version 6.2

As a couple of people have noticed, the recent release 6.2 of Notepad++ includes some significant changes to language definitions. The current QlikView language file will still work as before but menu access to the language definition screen has been moved under "Language" instead of "View". I'll update the installation instructions as soon as possible.

The new version of Notepad++ supports some new capabilities for defining languages and I'm working on a new version of the QlikView language file to make the most of them. Watch this space!

Monday 10 December 2012

QlikView for Notepad++ Formal Release 1.0

As there are no outstanding reported issues with the language definition for Notepad++ I am now happy to consider this the formal "stable" release of version 1.0.

Amazingly the previous versions have been downloaded over 1,200 times.

A few minor tweaks have been included in this release that weren't in version 0.4. Most notably REM was removed from the keywords list to prevent incorrect highlighting of it. Unfortunately, support for REM as a statement comment is not possible with Notepad++ language definitions.

You can find the download link and details on how to install or upgrade it here http://www.qlikviewaddict.com/p/qlikview-notepad.html. As before, if you find any issues leave me a comment below.

Tuesday 4 December 2012

QlikView Functions: dual()

As I touched upon in the first part of my series of posts on Dates in QlikView, one of the first things developers learn about QlikView is that there are no traditional data types. What they some times don't learn is that instead QlikView stores field data as dual values containing both a string, which is used for displaying to the user, and a numerical value used for sorting and calculations.

Let's consider a simple example of a field containing calendar months, where as well as the month name, QlikView also stores the number of the month.

NumberText
1Jan
2Feb
3Mar
......

Where possible, QlikView works out the text and numeric parts automatically for many data types when loading the data. All inbuilt QlikView functions also return a dual value and when passed a dual value they automatically use either the text or numeric part depending on what type of input they are expecting. Because of this automatic behaviour, many people are totally unaware that this is how QlikView stores field data. It is however a very powerful feature which adds to the speed and efficiency of QlikView. As a developer, being able to control this behaviour and define the dual values when QlikView can't work them out automatically would also be very powerful and thankfully this is indeed possible using the dual() function.

The dual() function allows us to specify the individual text and number parts to be combined into a single dual value:

dual(MyText, MyNumber) AS MyField
As I mentioned above, QlikView automatically works out which part of the dual value to use depending upon what you are asking it to do and so we normally don't need to give it a second thought. But again we can control this behaviour using the text() and num() functions should we need override and return one specific part:

= text(MyField)
= num(MyField)

There is one important point to note about QlikView automatically assigning dual values when loading data, which confused me for a while when I first came across it and I know has caught out many others. If I load 2 records into the same field for which the text part is different but the underlying number is the same, QlikView will assign the first of the text values it comes across to all following records where the numeric value is the same. So source data that looks like this:

NumberText
4Open
5Complete
5Closed

would be converted and stored in QlikView like this:

NumberText
4Open
5Complete
5Complete

As you can see, the last text value of "Closed" is replaced with the first value QlikView reads in for the same numeric value of 5, which happens in this example to be "Complete".

Once aware that this is how QlikView behaves you can use this to your advantage however. Because it prioritises the first text value it comes across, if you control which value this is, all other values with a matching underlying numeric value will be assigned to that first value and will result in a form of data cleansing. I'll leave it up to you to experiment and find ways to leverage this.

Because of it's simplicity and a lack of understanding on how QlikView stores data, the dual() function isn't used very often and so I thought it would be good to look at some practical examples of how it can be used and the benefits it gives. Hopefully these examples will make you consider how you could used it within your own applications.

Calendar Quarters

In the next part of my series of posts on Dates in QlikView, we'll look at separating a date into it's useful component parts, one of which is calendar or fiscal quarters. When using fiscal quarters particularly, it can be confusing to the user which calendar months fall within a given quarter and so we can use the dual function to give a useful text description to the user as well as retaining the underlying quarter number to use when performing calculations. One way to do this would be to use an inline mapping table like this:

QuarterMap:
MAPPING LOAD * INLINE [
    QuarterNum, QuarterDesc
    1, Apr - Jun
    2, Jul - Sep
    3, Oct - Dec
    4, Jan - Mar
]
;

Calendar:
LOAD
    Date,
    Month,
    Year,
    dual(applymap('QuarterMap', Quarter), Quarter) AS Quarter
RESIDENT MyData;

Assuming our underlying data table has a Quarter field containing the number of the quarter, we can use the applymap() function to look up the quarter number within the mapping table and return a more meaningful description. The dual() function then combines this description with the quarter number to make a single dual value field called Quarter. When displaying the field to the users, QlikView will use the text descriptions which are a lot more meaningful. When asked to perform a calculation using the field or when sorting the quarters, QlikView uses the underlying numeric value.

Shipping Statuses

This technique for using dual() was coined "Dual Flags" by Rob Wunderlich and is both simple and powerful. The basic principal is that rather than simply using 1 or 0 as a flag, you use dual() to create a dual value flag containing a more meaningful description also. For example, let's imagine we have a set of shipping data. Each record relates to an order that has either been shipped or not. Many would be tempted to create a simple flag to indicate the shipping status like this:

if(NOT isnull(ShipDate), 1, 0) AS Shipped
Instead we can use the dual() function as follows:

if(NOT isnull(ShipDate), dual("Shipped", 1), dual("Not Shipped", 0)) AS Shipped
This creates a dual value flag which opens up many more options for how we can use it. We can add the field in a list box which will display the more logical "Shipped" rather than a simple 1. All of the following would now also be possible and valid to use within expressions and/ or the script:

if(Shipped, ....
if(Shipped=1, ....
if(Shipped="Shipped", ....
count({$<Shipping={"Shipped"}>} ShipID)
sum(Shipped)

An interesting point to note here is that we can use sum(Shipped) rather than the less efficient count({$<Shipped={"Shipped"}>}ID) or even just count(Shipped). A sum is a simpler computational calculation than a count and so when working with very large data sets, the speed improvement of using sum() can be considerable.

CRM Statuses

I load data from a CRM system and each opportunity has a status such as "Lead", "In Progress", "Qualified", etc. Like the example of Quarters above, I can use a mapping table and applymap() to give each status an underlying number as well as the text description as follows:

StatusMap:
MAPPING LOAD * INLINE [
     OpStatus, OpStatusID
     Failed, 0
     Lead, 1
     In Progress, 2
     Qualified, 3
     Won, 4
     Closed, 5
]
;

Opportunities:
LOAD
     OpID,
     OpDate,
     dual(OpStatus, applymap('StatusMap', OpStatus)) AS OpStatus
RESIDENT MyData;

As before, when displaying OpStatus to the users we see the meaningful text description. But we can also use some interesting and powerful calculations because of the underlying number. For example we can count all active opportunities using the following simple set analysis:

count({$<OpStatus={">=Lead <=Won"}>} OpID)

I hope these examples show just some of the uses for the dual() function and that you might consider the benefits of using it in your QlikView applications going forward.

Friday 28 September 2012

QlikView for Notepad++ Update

Whilst working on the QlikView script highlighting for UltraEdit, I noticed a couple of minor omissions from the list of keywords I took from the language file I produced for Notepad++. So I've now update it and released a new version 0.4 of the language file.

You can find the download link and details on how to install or upgrade it here http://www.qlikviewaddict.com/p/qlikview-notepad.html. As before, if you find any issues leave me a comment below.

It's now been downloaded over 700 times, astonishing!

QlikView for UltraEdit

After releasing the Notepad++ language def, I was asked by one of my clients if it was possible to produce a similar syntax highlighting for UltraEdit which they use exclusively throughout their IT department. So after having a bit of a play, I've managed to create something which is on par with the Notepad++ highlighting.

Syntax highlighting in UltraEdit is a lot more capable than that in Notepad++ so I intend to investigate it a little further and see how close to QlikView's script highlighting this can get. I thought I'd release this initial version though for people to use and test.

Full instructions and screen shots on how to install it will come with the next release but for now you can download the QlikView UltraEdit Wordfile and it can be installed by copying the file into the folder "%appdata%\IDMComp\UltraEdit\Wordfiles\"

As always, if you find any bugs or have any suggestions, please leave a comment below.

Tuesday 14 August 2012

Stephen Few Dashboard Design Competition

Stephen Few has opened his 2012 Perceptual Edge Dashboard Design Competition. Anyone can enter and you can find more details on his Perceptual Edge blog http://www.perceptualedge.com/blog/?p=1308

Considering what people say about QlikView lagging behind it's competitors in the looks department, I'd love to see a QlikView entry win!

Monday 13 August 2012

QlikViewAddict.com - 7000 Monthly Page Views

Astonishingly, QlikViewAddict.com is now getting over 7000 page views a month.

For a change, I'm truly speechless! So all I'll say is a huge thanks to all of you who take the time to read my ramblings and I'll try and post a little more often.

Thursday 9 August 2012

Dates in QlikView - Part 2

In Part 1 of this series about dates in QlikView, we looked at how dates are stored in QlikView and how we can manipulate and perform some basic conversion on them. Those principals are critical to working with dates in QlikView and I'd strongly recommend you read it before continuing with this part.

A common problem occurs with dates in QlikView when an underlying system or database stores dates as strings or even as separate day, month and year fields rather than true dates. For example, if the dates are stored as strings, subtracting one from the other won't give you the number of days between the two. So ideally we need to convert the badly stored dates to true dates when we load them into QlikView, and luckily there are easy methods to solve this problem using a group of functions known as "interpretation functions".

Let's look first at how to create a true date from a string. Luckily, QlikView has a simple interpretation function that solves this problem for us. The date#() function interprets a string using the given format and creates a true date from it. Take special note of the #, its not a typo and differentiates it from the formatting function date() which we looked at in Part 1. Its straightforward to use as follows:

date#(DateStringField, 'YYYY-MM-DD') AS DateField

The function interprets the field DateStringField using the format YYYY-MM-DD and if it can do so, outputs a true date. Remember from Part 1 that QlikView stores dates as a dual value; the numerical representation of the date and a string representing how the date will be presented to the user. One thing worth noting about the date#() function is that the resulting date will be formatted using the same format used to interpret the string, which might not be the format you desire your dates to be displayed in. You should also remember that we can use the date() function to format the string part of a true date and so we can add it to the above code as follows:

date(date#(DateStringField, 'YYYY-MM-DD'), 'DD-MM-YYYY') AS DateField

Working with timestamps stored as strings is made just as simple in QlikView using the timestamp#() function instead. It works in exactly the same way and can be used like this:

timestamp#(TimestampStringField, 'YYYY-MM-DD hh:mm:ss') AS TimestampField

Again, you can wrap it in the timestamp() function if you need to format the resulting timestamp differently.

Great, we've solved the first problem, or have we? What if the field contains dates stored as strings of more than one format. For example, I may have dates stored as a mixture of formats such as YYYY-MM-DD, DD-MM-YYYY and DD/MM/YYYY. As with lots of things in QlikView, there is more than one solution but the simplest is a very underused QlikView function. The alt() function returns the first value passed to it that is numerical, and because a true date in QlikView is a dual value (part of which is a number) we can use it to return the first date#() format that manages to create a true date.

alt(date#(DateString, 'YYYY-MM-DD'), date#(DateString, 'DD-MM-YYYY'), date#(DateString, 'DD/MM/YYYY'), 'No Date Found') AS Date

If the first date#() function manages to interpret the string as a true date, then it will be returned, if not then it will move on to the next, and so on. If no numerical value is found, the alt() function will return the last value in the parameter list and so in this example, if it can't interpret the string using any of the given formats then it will return the string "No Date Found".

OK, so now we've resolved dates stored as strings, but what about our other problem of dates split between separate day, month and year fields. You might not see this as a problem at all, we often split up a date in QlikView into the day, month and year to make it simpler to use in charts, but it is vital to also have it at as true date if you want to be able to perform calculations with it. This is thankfully a really easy problem to solve using the makedate() function. It accepts up to 3 parameters, the year, the month (if omitted then Jan is assumed) and the day (if omitted the 1st is assumed) and creates a true date from them.

makedate(YearField, MonthField, DayField) AS Date

And of course there is also a maketime() function which can be combined with the makedate() function as follows to create a timestamp:

makedate(YearField, MonthField, DayField) + maketime(HourField, MinuteField, SecondField) AS Date

Remember that dates are integer (whole number) values for the number of days past the QlikView epoch date and time is a fraction of a day, and so we can simply add the two values together to create the timestamp. However, note that because we have used the + operator, QlikView assumes that you are only interested in a numerical answer and so we need to wrap the entire thing in the timestamp() function to format it correctly as a dual value like this:

timestamp(makedate(YearField, MonthField, DayField) + maketime(HourField, MinuteField, SecondField)) AS Date

Hopefully from this you can see that its relatively simple to create a true date from just about any way a date can be store in a database or other underlying system.

In the next part we'll look at how to perform more manipulations and complex calculations using dates, such a splitting up dates into separate fields, working out if a date is within the month to date, year to date, last year to date etc.

Friday 27 July 2012

QVExcel Now Free

Chris over at Industrial CodeBox announced this morning that their QVExcel product would now be available free to allow them to concentrate on their flagship QVSource.

For those that haven't come across it before, QVExcel has a rich set of features that enables you to create highly customised reports within Excel, but with the power of QlikView behind them. Here is a quick introduction video which shows you what can be achieved.



You can find out more about the free edition and download it here http://www.qvexcel.com/freeedition

Thursday 19 July 2012

Implementing QlikView Extension Objects

Extension objects are often seen an advanced QlikView topic and one which only an experienced developer can tackle. This isn't necessarily the case though and with a little explanation, anyone can build their first QlikView extension object. In his blog post, my good friend Stephen Redmond shows how to get a simple "Hello World" extension object up and running in a matter of second.

http://qliktips.blogspot.co.uk/2011/01/beginners-guide-to-qlikview-extension.html

And if you want to follow the rest of his explanations to build a more useful extension object you can find more via these links:

http://qliktips.blogspot.co.uk/2011/01/beginners-guide-to-qlikview-extension_09.html
http://qliktips.blogspot.co.uk/2011/01/beginners-guide-to-qlikview-extension_4612.html
http://qliktips.blogspot.co.uk/2011/04/beginners-guide-to-qlikview-extension.html

Tuesday 17 July 2012

QlikViewAddict.com is the First QlikView Blog for Amazon Kindle

OK, so I might not have been posting as much as I'd like lately but I have been busy behind the scenes on a number of new additions to the site. One of which I'm very proud to launch today.

QlikViewAddict.com is the first QlikView blog available for the Amazon Kindle. Its easy to subscribe and any new posts will be automatically delivered to your kindle for you to read at your leisure. For those who have the pleasure of sitting on trains a lot like myself, my Kindle is a lifeline to keep me sane. Unfortunately, there is a small monthly subscription fee of just 99p. I'd love to provide it free (Amazon actually won't let me) but the subscription fee covers the cost of delivering updates to your Kindle automatically.


I'm still fine tuning the integration with Amazon, so expect the odd formatting error but hopefully these should be ironed out very soon.

To subscribe, simply click the Amazon Kindle logo on the right navigation bar, or search for "qlikviewaddict" in the Kindle Store on your Kindle device.

Friday 13 July 2012

Dates in QlikView - Part 1

I've not been posting anywhere near as often as I would like, but in my defence I've have been rather busy trying to keep clients happy and make some progress on some new projects. So to get things rolling again here, I thought I'd write the first of a series of posts on dates. Don't worry, not the relationship kind, I'm not building a speed dating QlikView app, I tried but couldn't get hold of any data.

Handling date and timestamp data in QlikView seems to be one of the most common things people have difficulties with. It need not be difficult though and once you understand how dates are stored and a few simple facts about QlikView's inbuilt date and time functions you should have no problem at all. An important thing to note is that how QlikView stores dates is very similar to how most popular databases store them also and so the explanation I'm going to give here can be applied to some other technologies also.

The first thing you need to know about dates in QlikView is that they are really stored as a dual value. This value contains both a string (such as "01/01/2012") representing the date and number (such as "40909") defining the number of days since the epoch date. Dates prior to this base date are simply stored as a negative number of days. A common misconception is that the epoch date is 1st January 1900. What the epoch date is doesn't really matter to us when working with dates but for those that want to know, try creating a text object in QlikView and add the following expression:

=num(makedate(1900,1,1))

The answer you get back should be 2 and from that we can see that the QlikView epoch date is in fact the same as the Oracle epoch date, 30th December 1899.

What does matter to us when working with dates is being able to calculate simple things such as the number of days between 2 dates. Thankfully this is simple because of the way QlikView stores dates,  we can simply subtract one date from another to get our answer. QlikView is intelligent enough to know to use the number part of the stored date and the answer will give us the number of days between the two dates.

Timestamps are handled exactly the same as dates with one key difference, rather than being stored as an integer they include a decimal indicating the time as a fraction of a day.  For example, today's date (13th July 2012) would be stored as an integer of 41103 and so midday today (12:00pm) would be 41103.5 (midday being 0.5 of a day). 5:15pm today would be 41103.71875 and so on. Just like date, timestamps are stored as a dual value.

A common thing to want to do is to create a data from a timestamp, effectively removing the time. At first glance this might seem simple as follows:

date(MyTimestampField)

On the surface this appears to have worked correctly returning a date such as "13/07/2012". However, the date() function in QlikView is only a formatting function and thus affects only the string within the dual value stored. For example, midday today would be stored as both an numerical value of 41103.5 and a string "13/07/2012 12:00:00". Using the date function would not change the number but would change the string as expected to "13/07/2012". The problem with this is that when displaying values, QlikView will use the string if one exists, but when comparing values, QlikView will take the numerical value as priority. Because of this, two timestamps of different times on the same day may look the same when displayed, but will not be considered the same when QlikView is asked to compare them.

There is a simple solution to this problem, in fact there are 2 solutions. The first is to use the daystart() function which sets the time part of the timestamp (the decimal) to midnight (0). There is one issue with using the daystart() function and that is that it sets the string within the dual value as if it was a timestamp such as "13/07/2012 00:00:00". This can be solved by using the date() function to change the string formatting to just the date like this:

date(daystart(MyTimestampField))

The alternative method to convert a timestamp to a date is a little more old school but effectively does exactly the same thing. Because a timestamp is stored as a number with a decimal, we can use the floor() function to remove the decimal and thus remove the time. However, like using the daystart() function, the floor function outputs a number without a string representation of the date and so we must again us the date function to format it correctly in to a dual value like this:

date(floor(MyTimestampField))

Which method you choose to use is completely up to you, there is no noticeable efficiency benefit of one method over the other.

In the next part we'll look at what to do if your database isn't storing proper dates and how to use the date interpretation functions date#, timestamp#, makedate and maketime to solve this problem.

Tuesday 3 July 2012

Additional Gantt Chart Presentation

Chris over at oneqlikatatime.blogspot.co.uk has written a new blog post taking my early post on creating a basic gantt chart in QlikView and adding some additional design and presentation tweaks such as colouring the bars based on if they are active, past or pending. You can read it at http://oneqlikatatime.blogspot.co.uk/2012/07/qlikview-charts-with-colours.html

Saturday 23 June 2012

Opening a QVW Without the Data

I read a post on another blog earlier today about how to open a QVW file without its data and left a comment about an alternative way to achieve the same thing, so I thought I'd share it here too.

For those that have never come across this feature it can be useful when working with very large files that take a while to open and more importantly to recover a QVW file if the data in it has become corrupted for some reason. The latter is thankfully a rare occurrence but has been known. 

You can open a QVW without its data relatively easily by right clicking a document in the Recently Opened Documents list in the QlikView start page and selecting the option Open 'MyApplication' Without Data. The 'MyApplication' will be the name of the QVW file you selected.


If you've chosen to for the start page not to show you can get to it via the Help menu by selecting the option Show Start Page.

This method is fine if the document in question is one you've opened recently but if it's not then it won't be in your recent document list. Thankfully there is another way to open a document without the data using the command line parameter "nodata" as follows:

C:\Program Files\QlikView\qv.exe /nodata C:\MyFolder\MyApplication.qvw

If the code above appears on multiple lines then ignore that and type it all on a single line at the Windows command prompt. Obviously you'll need to modify the path to qv.exe and also the path to the file you are wanting to open.

A little tip that can come in handy once in a while.

Friday 15 June 2012

Automatically Leasing a License

Here is a quick QlikView tip for automatically leasing a license from a QlikView Server.

  1. Create a batch file containing the following:
    "C:\Program Files\QlikView\Qv.exe" /r "qvp://myQvServer/mySmallQvApp.qvw"
  2. Replace "C:\Program Files\QlikView\qv.exe" with the path to the qv.exe on your local computer.
  3. Replace "qvp://myQvServer/mySmallQvApp.qvw" with the server name and the name of a small application. You can always create a dummy application with no script or data just for this auto-leasing to use.
And that's it! You can schedule this to run on startup by placing the batch file in the Windows Startup folder, or over night each night using Windows Scheduler. The /r parameter asks QlikView to perform a reload of the application. As this is a server hosted document, QlikView won't be able to perform a reload and so will simply open QlikView, open the document and then close QlikView, leasing your license from the server in the process.

Tuesday 12 June 2012

Gantt Charts in QlikView

I've been asked more times than I can remember and seen countless posts on the QlikCommunity about how to produce gantt charts in QlikView. As with everything in QV there is more than one way to achieve a gantt chart but I thought I'd give a quick run through the most simple method.

The first thing you need is data, and more importantly data that contains 2 things, a start date and a duration. If you don't have a duration but you have an end date instead, you can calculate the duration by subtracting the latter from the former. For more on dates in QlikView see my earlier post on the subject. Once you have your data structured in this way, putting together the chart is simple. I used the following script to generate some random data for the charts shown in this post. Because the data is randomly generated, if you used this same script to experiment building a gantt chart with, don't expect your charts to show the same exact data.

Data:
LOAD
    TransID,
    chr(ord('A')+mod(TransID,26)) AS Category,
    date(monthstart(today()) + round(10*Rand1)) AS StartDate,
    ceil(10*Rand1) AS Duration;
LOAD
    rand() AS Rand1,
    recno() AS TransID
AUTOGENERATE 50;


Once I ran the this script I had a table of data that looked like this:


You can see I have the 2 key ingredients, a start date and a duration. With the data in place we can start to build our gantt chart. Added a new Chart object and QlikView should bring up its normal chart wizard for you to follow. The chart type we need to use is a bar chart and you may also want to give your chart a useful title before clicking "Next".


The first thing you need to do is add a dimension. This should be the field which contains the values for which you wish to have a bar in your gantt chart for each distinct value. This might be an activity or a project phase for example. In my example data I chose the Category field and clicked "Next".


Next we need to set our expression. The expression will give us the length of the bars displayed and because for a gantt chart we want this to represent the duration of the activity, we use our Duration field within the data. I used the following expression:

sum(Duration)
An important point to note is that I used the sum() function. This is because it may be possible to have more than one entry in my data for each Category and thus I wish to get the total duration of activities for the Category. Don't click on "Next" yet, we have one more thing to set about the expression.


As well as the length of the bar, we need to set the offset of the bars so that they don't all appear to start on the same date. To do this you need to click on the little + to the left of the expression you created do display the available expression properties. Within the "Bar Offset" property we need to define an expression to give us the starting position of each bar. In my example I used the following expression:

min(StartDate)
As before, I used the min() function to return the earliest date if there is more than one activity available for the Category. Once complete you can again click "Next".


You will now be asked to set a sort order for the dimension value should you need to. In my example I am happy with the default alphabetical sort order and so I clicked "Next" again to take me to the Style properties. The key to making my bar chart look like a gantt chart is to set the orientation to horizontal. You can choose to change to look and style of the bars if you wish to but for this example I left the defaults and clicked "Next".


There are a couple of presentation options that can make your gantt chart more user friendly. For example, I chose to limit the number of visible bars to 15 and to provide a scroll bar for users to view the rest should they wish. Don't click on "Next" just yet.


The other presentation option you may wish to add is a reference line showing today's date so that it is easy to identify within the chart. Click to add a new reference line and add the following expression:

=today()
You can change the look and feel of reference line if you wish but I left it standard for this example and clicked "OK".


Once back at the presentation options you can now click on "Next" to take you to the Axes settings. There is one crucial setting we need to change and that is to unselect the "Force 0" option for the expression. If this is selected the chart will start from the QlikView base date of 30th December 1899 rather than a more sensible date based on the data you wish to display. You may also wish to set static minimum and maximum values for the scale and a grid for the expression to make the chart easier to view. Once complete click "Next" again.


You should now be presented with the Colour options. By default your bars will all be the same colour so you can change this here should you wish. For this example I kept the defaults and clicked "Next" again to take me to the Number settings. As your durations are numerical, QlikView will display numbers for the dates along the expression axes by default. We need to change this by selecting the expression and changing the format to Date. If your data is more granular you might want to use the Timestamp format instead to include time. We don't need to worry about any more settings in the wizard so we can click "Finish".


And that's it, you should hopefully have something that looks a little like the example I ended up with shown below. There are many other options to make your gantt chart more user friendly should you wish. For example you might want to include the duration of the bars as text on the data points or even as a popup. Have a play, the options are almost endless.



As I mentioned at the start, there are alternatives to achieve gantt charts in QlikView. For example, you could also use a third party charting tool to produce the gantt chart and embed it inside your document using an extension object. The method above is what I prefer to use and is the simplest to achieve using QlikView's inbuilt functionality.

Happy Gantting!

Thursday 7 June 2012

QViewer Review

I posted yesterday about downloading the newly released QViewer that allows you to view the contents of a QVD file without having to load it into a QlikView application. QViewer was produced by Dmitry Gudkov, a QlikView consultant from the Ukraine and released on his blog a couple of days ago. If you haven't yet, you can read his release post and also find a download link here http://bi-review.blogspot.co.uk/p/fast-qvd-viewer.html

It's worth pointing out that this is a demo version and Dmitry has this to say about it:
This is a technology demo which will be replaced with more functional self-updated version later.
So you can't expect a totally polished application, but from the few tests I've run with it so far, it's been pretty good.

The download is a Zip file and contains a number of files including a setup.exe. I extracted the archive and ran the executable. The install was quick (the entire Zip is only 230KB) and the application started itself afterwards. A simple menu across the top of the QViewer window presented me with the option to open a file. I clicked it, navigated to a QVD file and within a few seconds was presented with the contents of the file.


The next menu item on the menu allowed me to view the meta data for the QVD I had opened. As expected, clicking it presented me with another window telling me lots of useful information including the number of rows, the number of columns, the number of distinct values in the fields.


As well as this basic meta data, clicking on a column in the main QViewer window presented me with a small window showing all the distinct values within that column and more importantly, gave me the option to search within it.


It's worth mentioning that this search only effects this window and not the main QViewer window and so you can't use it to search for specific records, only distinct values within the selected field. Maybe that will be a good addition for the final version.

The only real issue I've found so far is that when opening a very large QVD (in excess of 1GB) I received an "Out of Memory" exception. This is likely because the application is compiled as a 32bit version and it simply hit the 2GB limit a single process can address on 32bit windows. Dmitry suggests that from his tests, a memory usage will be approximately 3 times the size of the QVD.

That aside, this is a great little tool and I think this will become even more useful with some tweaks and a 64bit version for the final release. I'll certainly be adding it to my QlikView toolbox.

QlikView for Notepad++ Update

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

I'm amazed to see that the QlikView language definition for Notepad++ has been downloaded over 200 times. Good to see there are so many people making use of it.

For those who haven't yet tried it you can read the instructions from the original post http://www.qlikviewaddict.com/2012/04/qlikview-scripts-in-notepad.html

For those that already have the first release you will need to download the new language definition file http://qvacb.googlecode.com/files/qlikview-lang-def-v0.3.xml

You'll need to follow pretty much the same steps as before but remove the old QlikView language definition file before importing the new one. You can do this by selecting "QlikView" in the User language drop down at the top of the User Defined Language dialog and then click "Remove". You can then import the new file following the same steps as before.

As before, if you find any issues leave me a comment below.

Wednesday 6 June 2012

QViewer

I'm just downloading the QVD Viewer produced by Dmitry Gudkov. It promises to allow you to open and view the contents of a QVD without having to load it into QlikView. Watch this space for how I get on but if you'd like to have a go yourself you can find more information and a download link at http://bi-review.blogspot.co.uk/p/fast-qvd-viewer.html

Wednesday 23 May 2012

QlikView Comparative Analysis

QlikTech released this video on their YouTube channel earlier today. It demonstrates how to set up comparative analysis using the Alternative States capabilities of QlikView v11. It's worth a watch if you aren't familier with Alternative States and how they work.

Wednesday 9 May 2012

Becoming a QlikView addict

My introduction to QlikView isn't that different to many others. I started out as a customer back in 2005, QlikTech had just opened their UK office and there wasn't many people around who you could describe as an expert in QV. Because of this we made a ton of mistakes along the way and banged out heads of a fair few walls too. But for me the most interesting part of  getting to grips with QV came before we'd even brought the software.

My role at the time made me the interface between the outsourced IT partner and the business, running around trying to tie everything together to ensure projects got delivered and if possible on time and budget. One day a senior manager dropped me an email with a link to the QlikView website and a note asking me to let him know what I thought. He'd been told about QlikView and wanted to know if it could do what the "marketing gumf" said it could. After a quick read through the material I replied to the email with a large dose of scepticism but was told they were coming in to give us a demo.

Back then, BI tools were all what I'd now refer to as "Traditional BI". Complex, cube based tools that required a life time and a small army to implement successfully. I'd worked on a couple of BI projects and didn't have a very high opinion of their value for money. The day of the demo dawned and I'd been having a terrible week. Several projects had hit problems, I was getting pressure from all sides and I could have done without losing an hour to someone trying to sell me a tool I was convinced we didn't need. In the end I resolved myself to the fact that at least I'd be able to ask a few technical questions and enjoy watching them squirm trying to answer.

What happened instead wasn't so much a demo as an hour of consulting and it truly blew me away. This pre-sales consultant sitting before us had managed in an hour to show us how to load a relatively large volume of data from our own systems into QlikView and built some rudimentary charts on top. Admittedly the end result wasn't pretty or efficient but it worked and if that could be done in an hour, pretty and efficient couldn't take long either. I was hooked on the spot and went away with a trial license in my pocket (long before the days of personal edition) and an intent on having a go myself. The rest as they say is history and needless to say it didn't take us long to decide to buy.

What made me decide to write this post was a discussion I was having with a customer last week who had recently purchased QlikView. We were discussing his introduction to the tool and how his experience was different from my own. There are now a plethora of traditional BI, data analysis and visualisation tools available with each claiming to be better than the rest. Customers have seen and done it all before and so achieving that same awe factor is much much harder.

Over the last few days since, I've continued to play over this idea in my mind. The conclusion I've come to is that whilst some aspects of introducing a person to QlikView may be different today, there are a lot of similarities too. And most importantly of all I am convinced that the similarities are the most critical aspects of the process. Much like I thought, most potential customers believe they have seen and heard it all before; that fundamentally all BI tools are the same. They don't want to be sold to, are too busy to listen to yet another pre-rehearsed sales pitch, and have other problems they'd rather be concentrating on.

What any QlikView salesperson needs to do most of all is to NOT sell QlikView. No, I've not gone mad (I've been that way for quite a while), what they need to do is to help the customer. Don't waste their precious time, help them to understand how QlikView can instead save them time and a massive headache. Make it relevant to them; anyone can demo a pre-made application that they know inside and out. If possible, build something with their own data, or at the very least have a demo application that is relevant to what they do and don't just demo it, develop it. They don't want to believe you, why the hell should they, they want to see for themselves.

QlikView must stand apart from the mass of BI tools available. Forbes last week placed QlikTech at number 3 in their Fast Tech 25 List of the fastest and strongest growing companies in the US. They come behind only Apple and LinkedIn and ahead of many great technology companies. They are also the only BI software vendor listed. Help the customer to see this for himself, and I don't mean send him the think to the Forbes website, I mean SHOW him how QlikView is different. Again, don't just tell him and expect him to accept you know what you are talking about, he doesn't know you anymore than the other sales people who cross his path on a daily basis.

Looking back, that's basically what the pre-sale consultant did for me. He helped me to see how QlikView was different, how it could be implemented so quickly, efficiently and not just be another expensive, painful IT project for our business. That was why I was blown away in 2005, and that's not changed an iota between then and now.

Thursday 3 May 2012

QlikView for Notepad++ Update

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

Since so many people seem to have downloaded the QlikView language definition for Notepad++ that I released earlier this week, I thought I'd better finish adding the keywords and statements that were missing. It should now support ALL keywords and statements as well as all functions and line and block comments.

For those who haven't yet tried it you can read the instructions from the original post http://www.qlikviewaddict.com/2012/04/qlikview-scripts-in-notepad.html

For those that already have the first release you will need download the new language definition file http://qvacb.googlecode.com/files/qlikview-lang-def-v0.2.xml

You'll need to follow pretty much the same steps as before but remove the old QlikView language definition file before importing the new one. You can do this by selecting "QlikView" in the User language drop down at the top of the User Defined Language dialog and then click "Remove". You can then import the new file.

As before if you find any issues leave me a comment below.

QlikView Power Tools v1.1 Released

An updated set of QlikView Power Tools has been released including a couple of new tools:
  • QlikView Server Super Agent - A monitoring tool for QlikView Server, that monitors Qv services regardless of version (9+) and reports any service outage with email notifications and/or log files
  • Qv Server Object Connector - A custom data connector for QlikView that much like the SharedFileViewer provides insight into .shared files, but instead allows to load data straight from .shared files into QlikView documents for analysis. The tool supports extraction of data like ownership, size and type of objects, expressions, annotations, bookmark selections (fields and values) and much more.
And updated versions of the following tools:
  • QlikView Server Agent
  • Qv User Manager
  • Server Object Handler
  • Shared File Viewer
You can read more details and download the Power Tools from http://community.qlikview.com/message/215866#215866

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.

Wednesday 28 March 2012

Creating Empty Tables

I've had a couple of conversations over the last week or so on the subject of building scripts to be as robust as possible. It's a pretty large subject though and would be a huge post, so instead I've decided to pick out one topic in particular that came up and leave the rest for another day.

You'll have guessed from the title of this post that the topic I've chosen is around how to create an empty table in QlikView, and more importantly, why the hell would I want to anyway.

Let's consider a scenario, I have a number of spreadsheets containing budget data, they all have a similar structure and each spreadsheet contains a single month's budget. The file names all use the same naming convention starting "Budget" followed by the year and month for which they hold data (eg. "Budget 2012 Jan.xls").

My first thought might be to simply use a wild card load in QlikView like this:

MyBudgets:
LOAD
    *
FROM [Budget*.xls] (biff, embedded labels, table is Budget$);


There us one problem with this though, the keen eyed will notice I said that the files have a similar structure which would suggest they aren't all exactly the same. Because of this a wildcard load would fail. Even if it did work, without the same structure the files wouldn't be concatenated into the same table anyway.  

Alternatively I could choose load the files in one-by-one as I know their file names, concatenating them into the same table to use in my app. But this would mean that if I add more budget files in the future, I have to modify the script to add the new tables and this would create a high maintenance app.

Instead I'd like to loop through all the available files and load them into the same table which is simple enough to do in QlikView like this:

FOR EACH File IN filelist('Budgets\Budget*.xls')
    CONCATENATE(MyBudgets)
    LOAD
        *
    FROM [$(File)] (biff, embedded labels, table is Budget$);
NEXT


There is a problem with this code though, the table MyBudgets doesn't yet exist and thus, for the first file it tries to load, the CONCATENATION statement will fail. As with everything in QlikView there is more than one solution to this problem. One approach is to check within the loop if the table exists and if not then create the table instead. This is a perfectly valid method but doubles the amount of code I need to write. My preferred is to create an empty table called MyBudgets first so that the loop can simply concatenate all files to it.

Creating an empty table in QlikView is really simple using an inline load statement. Going back to my scenario, I know that the budget files all contain the field BudgetID and so I can create an empty MyBudgets table containing that field and then concatenate all my files to it like this:

MyBudgets:
LOAD * INLINE [BudgetID];

FOR EACH File IN filelist('Budgets\Budget*.xls')
    CONCATENATE(MyBudgets)
    LOAD
        *
    FROM [$(File)] (biff, embedded labels, table is Budget$);
NEXT


Problem solved! You can use this trick even if you don't know the name of a field. Simply create the empty table with a dummy field name such as "MyDummyField" and then drop the field after you've concatenated the tables to it.

Monday 19 March 2012

When QlikView Data Types Go Wrong

Writing the previous post Removing Leading Zeros reminded me of a lesson I learnt (the hard way) a few years ago about how QlikView perceives data types.

Anyone who has worked with QlikView for more than a few days will have realised that you don't have to explicitly tell it which data type a field contains. Instead, QV decides for itself and it does this at a value level rather than having a single data type for every entry in a field. Normally this makes a developer's life very simple and helps to speed up the process of building data models. I'm sure no one, including myself, would argue it is a bad thing but there is one situation where this can cause you a huge headache.

QlikView is possibly a little too clever when it comes to what it considers to be a number, and as well as looking for values which are composed of all digits, it looks for other possible number formats also. For example, QlikView considers that a value of 5E3 means 5 times 10 to the power of 3 which if you perform the math is 5000 in digit form.

On the surface this might not seem a problem but a few years ago I came across a customer who was complaining of lost products in their QlikView application and after scratching my head for 2 days trying to find the problem I finally discovered the cause of the issue. The client's product IDs where made up of 6 hexadecimal characters. For those who aren't familiar with hexadecimal, as well as the numbers 0 to 9, hex uses the letters A through F also and this obviously means its perfectly possible to have values such as 0005E3. And as you will have guessed by now, this was considered the same product as 005000.

What makes this problem even more difficult to find is that QlikView displays whichever of two values it happens to come across first. So if it finds product 0005E3 first, product 005000 seems to have disappeared and its data is linked to the former. If it comes across product 005000 first, product 0005E3 seems to have disappeared instead.

Thankfully fixing the problem is a lot easier than finding it and simply wrapping the field in the text() function forces QlikView to consider them as strings rather than numbers and thus won't consider the values as the same.

text(ProductID) AS ProductID

The chances of you stumbling upon this same issue are pretty slim but if you ever do, knowing this will hopefully save you a lot of time and a very real headache. That aside, hopefully I've made you think a little about how QlikView interprets the data you give it.

Wednesday 14 March 2012

Removing Leading Zeros

I was talking to someone this morning who asked me if it was possible to remove the leading zeros coming from his data source. After an initial "yes of course" followed by a longer period of thought, the solution we ended up with was as follows:

replace(ltrim(replace(MyField,'0',' ')),' ','0') AS MyField

This code first uses the replace() function to replace all zeros in the field with a space and then uses the ltrim() function which removes spacing from the begining of a string only. We then use replace() again to convert any remaining spaces back to zeros.

Thursday 8 March 2012

Inline Load Trick

Even the newest of QV developers have likely used an inline load and be familiar with the below screen. For those that might not have come across them, inline loads allow you to define a table of data within the QV script. You can use to wizard to create them which is available from the Edit Script dialog by using the menu, Insert -> Load Statement -> Load Inline.



There is however a feature of the Inline Data Wizard that often gets overlooked. If you look you will notice a Tools menu containing only a single tool, "Document Data...". Clicking on this opens up a new window like this:


This gives you the option of inserting values from existing fields in the data model. Please note, QlikView can't guess what the values will be when the script is next run so will show you the fields and values currently available in the app from the last time the script was run successfully. Clicking OK will paste the values into the Inline Data Wizard window.


And it's as simple as that. I most commonly use inline loads to create small, static mapping tables and this obviously means taking data from the existing data model and mapping them to groups or new values. This little trick is perfect for this and can save you having to create a temporary list box in the front end t copy the values from.

Wednesday 7 March 2012

Looping Through Excel Sheets

I answered a question on the QlikCommunity the other day and it reminded me of something I've not done for a little while, looping through sheets in an Excel file within a QV script.

The key to achieving this is to use an ODBC connection to connect to the Excel file first, and then use the SQLTables command to return a table listing the sheets (tables) within the file. Once you have this you can loop through this table and load the sheets like this:

ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx];

XlsTables:
SQLTables;

DISCONNECT;

LET vRows = NoOfRows('XlsTables');

FOR i = 0 TO $(vRows)-1
    LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);

    $(vSheetName):
    LOAD
        *
    FROM [ExcelFile.xlsx]
    (ooxml, embedded labels, table is '$(sheetName)');
NEXT i

DROP TABLE XlsTables;


This little trick can come in handy and with a few tweaks it can also address a number of other senarios for dealing with Excel spreadsheets such as:
  • What if I want to load from an .xls file instead of a .xlsx file? Not a problem, simply change the Connect and Load statements accordingly.
  • What if I want all the sheets loaded into a single table? You can create a blank table first, then use the above to concatenate the sheets into it rather than into their own named tables.
  • What if the Excel file always has one sheet but it is named differently each time? Tweak the above to load the sheet into a known table in QV.
  • What if, rather than loading all sheets from the file, I want to load only those which start with a known string (eg. "My Data Tab 01/01/2012", "My Data Tab 01/02/2012", etc). Simply wrap an IF statement around the LOAD to check if the table name starts with the required string.
I've probably only ever used this trick a handful of times, but its a very useful tool to have in your pocket should you need it.

Friday 2 March 2012

Explaining Joins

Those familier with manipulating data in SQL or other BI technologies should have no problem understanding the different types of joins available in QlikView. But those who aren't sometimes need a little explanation and it's a topic that I've fond myself explaining often when training people.

The four primary logical join types, LEFT, RIGHT, INNER and OUTER are supported in QlikView. Let's consider the following eample tables and what the output for each will be:

Field1Field2
A1
B2
C3
Field2Field3
2X
3Y
4Z

As you can see, both tables contain a column called Field2 for which some, but not all, of the values have a matching entry in the other table. The syntax for performing a join in QlikView is relatively simple as follows:

MyTable:
LOAD
    Field1,
    Field2
FROM myqvd1.qvd (qvd);

LEFT JOIN (MyTable)
LOAD
    Field2,
    Field3
FROM myqvd2.qvd (qvd);


It is important to note that QlikView links records based on matching field names in the two tables being joined. In the example above, both tables contain a field called Field2 and thus QlikView will look for matches across these fields.

Also worth noting is the the table name defined in curved brackets. This defines the previously loaded table which the following table should be joined to. It is not mandetory but it is considered best practice to always specify a table to join to inorder to avoid confusion or mistakes. Without it, QlikView joins the table of data to the previously loaded table.

LEFT JOIN

In a LEFT join, all the records from the first table are kept, and only those records from the second table that match a record in the first table are kept. Using the above example tables, the output would be as follows:

Field1Field2Field3
A1
B2X
C3Y

You will notice that there is a blank entry in Field3. This is because no matching record exists in the second table. You will also notice that the record in the second table for which there is no match in the first table has not been included.

RIGHT JOIN

In a RIGHT join, all the records from the second table are kept, and only those records from the first table that match a record in the second table are kept. Using the above example tables, the output would be as follows:

Field1Field2Field3
B2X
C3Y

4Z

You will notice that there is a blank entry in Field1. This is because no matching record exists in the first table. You will also notice that the record in the first table for which there is no match in the second table has not been included.

INNER JOIN

In an INNER join, only records with a match across both tables will be kept. Using the above example tables, the output would be as follows:

Field1Field2Field3
B2X
C3Y

You will notice that there are no blank entries. Records from either table for which a match was not found have not been included.

OUTER JOIN

In an OUTER join, All records will be kept and where possible, records will be matched. Using the above example tables, the output would be as follows:

Field1Field2Field3
A1
B2X
C3Y

4Z

You will notice that there are blank entries in both Field1 and Field3. This is because a matching record was not found in the other table.

When Joins Go Wrong

The most common and easily made mistake when joining tables is not having at least one field named the same in both tables. When this occurs, QlikView can't make any matches within the data and so outputs all possible combinations of the records in both tables. This is known as the cartesian product. If we take the above example but rename Field2 in the second table by mistake so it no longer matches the first table like this:

Field1Field2
A1
B2
C3
Field22Field3
2X
3Y
4Z

When performing any of the join types on these two tables, QlikView would create the following result:

Field1Field2Field22Field3
A12X
A13Y
A14Z
B22X
B23Y
B24Z
C32X
C33Y
C34Z

The produced table contains all possible combinations and therefor the number of rows it contains will be the number of records in the two tables multiplied together. In this example 3 x 3 = 9 records. This isn't the end of the world when data sizes are relatively small, but when mistakes are made joining tables containing millions of records, the resulting table can take a very long time to calculate and be so large that it uses up all the physical RAM and result in the machine becoming unresponsive. Anyone working with QlikView on large datasets for very long will have learnt this lesson the hard way. I remember making a mistake whilst joinging a billion record table to itself and had to explain red faced to the customer's IT department why I needed them to restart the non-responsive server as it tried to calculate the result.

A cartesian product isn't always the result of a mistake though and can also be performed on purpose in advanced data transformations. For example, if I have a calendar table with a record for each day but what I really need is to expand it to have a record for each 8 hour shift in each day, there is more than one way to approach the problem. I could load the calendar into a temp table and then concatenate it 3 times into a resulting table denoting the shift each time, or I could create a Shifts table containing a record for each shift and join it to the calendar table providing no matching field and thus purposfully causing a cartesian product.