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!
Friday, 28 September 2012
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.
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!
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.
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:
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:
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:
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.
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.
And of course there is also a maketime() function which can be combined with the makedate() function as follows to create a timestamp:
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:
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.
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 DateFieldThe 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 DateFieldWorking 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 TimestampFieldAgain, 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 DateIf 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 DateAnd 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 DateRemember 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 DateHopefully 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
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
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
Subscribe to:
Posts (Atom)