- Added Qlik Sense connection string support
- Added the missing keywords DERIVE and FLUSHLOG
- Added support for DIRECT QUERY statements
- Fixed issue with REM comments not supporting whitespace before them
- Fixed issue with clicking cancel on shortcode prompts results in shortcode still being entered
- Fixed issue with variable declarations containing a full stop (AKA period) not highlighting correctly
- Fixed issue with backslash incorrectly operating as an escape character in strings
- Other minor highlighting improvements
- Significant speed improvements
- Added custom vector font to allow insertion of some Qlik icons
- Added the LUIicons vector font from LeonardoUI to allow insertion of all Qlik Sense UI icons
- Added optional extra line numbers to code block (should be used with caution as can prevent correct highlighting of code than spans multiple lines eg. /* */ block comments)
Showing posts with label expressions. Show all posts
Showing posts with label expressions. Show all posts
Wednesday, 14 February 2018
Qlik Web Syntax Highlighter v2.0
Qlik Web Syntax Highlighter v2.0 has just been released. This release provides the biggest improvements to date, bringing a large number of new features as well as the usual tweaks and bug fixes and follows:
Wednesday, 17 January 2018
Qlik for WordPress Plugin v1.2
Qlik for WordPress Plugin version 1.2 has been released and included the following updates:
- Minor bug fixes
- Update to the Highlight.js core
Tuesday, 16 January 2018
Qlik Web Syntax Highlighter v1.2
Qlik Web Syntax Highlighter version 1.2 has been released and included the following updates:
A new version of the Qlik for WordPress Plugin based on the Web Syntax Highlighter is planned for release shortly.
- Minor bug fixes
- Update to the Highlight.js core
A new version of the Qlik for WordPress Plugin based on the Web Syntax Highlighter is planned for release shortly.
Thursday, 12 February 2015
QlikView for WordPress Plugin v1.1
QlikView for WordPress Plugin version 1.1 has been released and included the following updates:
- The highlight.js core has been updated to the latest release of highlight.js.
- Improvements to the identification of field names in both script and expressions.
- Added missing IN keyword.
- Added missing filelist() function.
- Other minor highlighting improvements.
Sunday, 19 October 2014
QlikView Web Syntax Highlighter v1.0
Hot on the heals of last week's release of QlikView for WordPress, I'm pleased to announce the first full release of QlikView Web Syntax Highlighter v1.0 which the WordPress plugin is based on.
QlikView Web Syntax Highlighter provides basic syntax highlighting of QlikView script and expressions within web pages. It utilises a custom build of Highlight.js to apply the highlighting dynamically when the web page is viewed.
This version includes the following functionality:
QlikView Web Syntax Highlighter provides basic syntax highlighting of QlikView script and expressions within web pages. It utilises a custom build of Highlight.js to apply the highlighting dynamically when the web page is viewed.
This version includes the following functionality:
- Highlighting of all current (v11.20) functions
- Highlighting of all current (v11.20) keywords and statements
- Highlighting of line comments (//) and block comments (/* */ and REM ; )
- Highlighting of variable definitions (SET and LET)
- Highlighting the usage of variable within dollar-sign expansion $( )
- Highlighting of field names in most situations
Friday, 17 October 2014
QlikView for WordPress Plugin v0.2
As you may well have seen already, yesterday I released the first official pre-release of QlikView for WordPress plugin. This plugin brings syntax highlighting of QlikView script and expressions within posts and pages to WordPress blogs and websites.
The syntax highlighting is currently an approximation of that within the QlikView script and expression editors. It is however expected that the highlighting will be improved in later versions. The next version will include significant improvements to highlighting of expressions as well as some minor bug fixes.
Downloads and full instructions for installation and use can be found on the QlikView for WordPress page, or alternatively on the WordPress.org website.
The syntax highlighting is currently an approximation of that within the QlikView script and expression editors. It is however expected that the highlighting will be improved in later versions. The next version will include significant improvements to highlighting of expressions as well as some minor bug fixes.
Downloads and full instructions for installation and use can be found on the QlikView for WordPress page, or alternatively on the WordPress.org website.
Thursday, 9 October 2014
Coming soon - QlikView Syntax Highlighting for the Web
I thought I'd let you all in on another little project I've been tinkering with on the side. I have had a long standing problem when blogging about QlikView. Most of my posts contain snippets of QlikView script or expression code which can be very hard to read if it is not syntax highlighted in at least an approximation of how it is highlighted within QlikView. I considered using images taken from screen grabs of the code in QlikView, but this means readers can't copy and paste the code into their QlikView projects making it less than ideal. And so I am left with the time consuming task of having to manually highlight code snippets in my posts, and this means less time for new posts.
//Define the table name
SET vMyTable = 'MyTable1';
//Load in the table of data
$(vMyTable)_Temp:
LOAD
A,
B,
text(C) & ' a string' AS C
RESIDENT $(vMyTable);Thursday, 26 June 2014
QlikView Funtions: today() and now()
In this post I want to take a look at two very closely related functions, today() and now(). Many of the more advanced calculations that we want to perform with dates and times require us to know what date it is, and what time it is.
First things first, let us take a look at what the help says about these two functions:
0 Date at script run
1 Date at function call
2 Date when the document was opened
Default timer_mode is 2. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.
0 Time at previously finished reload (not currently ongoing reload)
1 Time at function call
2 Time when the document was opened
Default timer_mode is 1. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.
First things first, let us take a look at what the help says about these two functions:
today([timer_mode] )
Returns the current date from the system clock. The timer_mode may have the following values:0 Date at script run
1 Date at function call
2 Date when the document was opened
Default timer_mode is 2. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.
now([timer_mode] )
Returns a timestamp of the current time from the system clock. The timer_mode
may have the following values:0 Time at previously finished reload (not currently ongoing reload)
1 Time at function call
2 Time when the document was opened
Default timer_mode is 1. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.
Sunday, 17 March 2013
Dates in QlikView - Part 3
It's taken me a little longer than I wanted to complete this next instalment of my series of posts on dates in QlikView. I wanted to get the post about the dual() function finished first as it can be very useful when working with dates and is referenced below. My work commitments keep getting in the way too. Anyway, let's crack on...
In the first 2 parts of this series, I looked at how dates are stored in QlikView and how to ensure you have them in the right format to be most effective. So now that we have that out the way, lets looks at how we can start to make dates more useful for using in charts and objects within the front end of QlikView.
Despite looking in part 2 at how we worked to combine parts of a date stored separately in order to create a "true date" from them, we often want to do the reverse also and split the date into its composite parts. For example, we might want to be able to select individual years without having to select every date in those years, or we might wish to be able to display a bar chart showing our sales by month.
In the first 2 parts of this series, I looked at how dates are stored in QlikView and how to ensure you have them in the right format to be most effective. So now that we have that out the way, lets looks at how we can start to make dates more useful for using in charts and objects within the front end of QlikView.
Despite looking in part 2 at how we worked to combine parts of a date stored separately in order to create a "true date" from them, we often want to do the reverse also and split the date into its composite parts. For example, we might want to be able to select individual years without having to select every date in those years, or we might wish to be able to display a bar chart showing our sales by month.
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, 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:
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:
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:
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:
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.
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.
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.
With this in mind you might think you could also use the null() function to test if a field contains Null like this:
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:
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:
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:
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:
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:
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:
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:
And a quick word of warning. You may see the following syntax to exclude Nulls written sometimes on blogs and forums:
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.
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 SalesValueWith 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 SalesValeGreat, 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 SalesValueThe 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 GrossSalesValueIf 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 GrossSalesValueCounting 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.
Friday, 17 February 2012
Compression in QV
I was talking to someone this morning who mentioned how amazed they were at the compression QlikView managed to achieve. This seems to be a comman misconception about QlikView, so after explaining this to him, I thought I'd repeat my explanation here also.
So why then is a QVD so much smaller than a CSV containing the same table of data or why is a QVW so much smaller than several QVD's concatenated together into a single table? The answer lies in understanding how QlikView stores data and the key to this is understanding some basic facts about QlikView's Associative In-Memory Technology. This is what QlikTech have to say about it:
You can even use this fact to your advantage when dealing with large volumes of data in QlikView. Breaking down a field into several fields can cause more repetition and thus reduce the amount of resources QV needs to hold the data both in RAM and on disk. Email addresses are prime candidates for this technique as the domain part will likely not be unique to one entry. Take this example:
QlikView will consider each of these values as unique and thus will be storing the "@mycompanydomain.com" part repeatedly. If you were to split the email addresses at the "@" then the domain part becomes the same for all 3 records and thus QV will store it only once. You can split the email address using the following lines within a load statement:
When wanting to display the email address in a chart you only have to concatenate the 2 fields together remembering to replace the '@' in the middle like this:
The same technique can be used for mail addresses, phone numbers, part codes and any other patterned, repetitive data.
FACT: QlikView does not use compression for QVDs!
So why then is a QVD so much smaller than a CSV containing the same table of data or why is a QVW so much smaller than several QVD's concatenated together into a single table? The answer lies in understanding how QlikView stores data and the key to this is understanding some basic facts about QlikView's Associative In-Memory Technology. This is what QlikTech have to say about it:
"QlikView uses an associative in-memory technology to allow users to analyze and process data very quickly. Unique entries are only stored once in-memory: everything else are pointers to the parent data. That’s why QlikView is faster and stores more data in memory than traditional cubes."So from this you can easily deduce the reason QlikView appears to compress data so much is that it stores only unique values thus removing repetition in the data. Think about it for a second, if I have a million rows of data but a field contains only 10 unique values, then QlikView stores those 10 values only and not a million values.This applies equally to the RAM QlikView will need to hold the data as well as the hard disk space needed to store the QVW and QVD files.
You can even use this fact to your advantage when dealing with large volumes of data in QlikView. Breaking down a field into several fields can cause more repetition and thus reduce the amount of resources QV needs to hold the data both in RAM and on disk. Email addresses are prime candidates for this technique as the domain part will likely not be unique to one entry. Take this example:
| EmailAddress |
|---|
| bob.jones@mycompanydomain.com |
| jane.smith@mycompanydomain.com |
| paul.hill@mycompanydomain.com |
QlikView will consider each of these values as unique and thus will be storing the "@mycompanydomain.com" part repeatedly. If you were to split the email addresses at the "@" then the domain part becomes the same for all 3 records and thus QV will store it only once. You can split the email address using the following lines within a load statement:
....
left(Email,index(Email,'@')-1) AS EmailName,
right(Email, len(Email)-index(Email, '@')) AS EmailDomain
....When wanting to display the email address in a chart you only have to concatenate the 2 fields together remembering to replace the '@' in the middle like this:
EmailName & '@' & EmailDomain
The same technique can be used for mail addresses, phone numbers, part codes and any other patterned, repetitive data.
Wednesday, 15 February 2012
Excluding Zeros in Set Analysis
I've been reading a lot of historic posts on the QlikCommunity (yes I need to get out more) and came across an interesting question, how to exclude zeros when performing an average calculation in an expression. The offered solution is a noteworthy snippet:
This is a perfect solution but it got me thinking about the difference between "-=" and "=-". The former will take current selections in the Value field and then remove zeros. The latter will ignore current selection in the Value field and remove zeros from all possible values. To avoid the confusion you could use the following syntax instead:
This method would give the same answer but be careful if you try to add other selections to the set modifier as you would remove more data along with the zeros.
avg({< Value -= {0} >} Value)This is a perfect solution but it got me thinking about the difference between "-=" and "=-". The former will take current selections in the Value field and then remove zeros. The latter will ignore current selection in the Value field and remove zeros from all possible values. To avoid the confusion you could use the following syntax instead:
avg({$-< Value = {0} >} Value)This method would give the same answer but be careful if you try to add other selections to the set modifier as you would remove more data along with the zeros.
Subscribe to:
Posts (Atom)