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