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:

• 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 

As always, grab the download link and full instructions from the QlikView Web Syntax Highlighter page. If you find any issues or have any suggestions, please leave me a comment below.

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.

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);

QlikView UltraEdit Wordfile v2.0

QlikView language definition for UltaEdit v2.0 has been release.

This version is a huge leap from the first release and so has been labelled as a new major release. Some of the new features include:

• Missing keywords added including TABLES, FIELDS and UNTIL
• Store statements, Qualify and Unqualify statements, info loads, mapping loads, join and keep statements are all now identified in the UltraEdit Function List
• REM has been added as a block comment. Note, unlike QlikView, UltraEdit can't apply a REM comment only if it appears at the start of a line and thus REM comments will be highlighted wherever they appear.
• Added UltraEdit templates for commonly used statements and code snippets including:
  •   Binary load
  •   Include statement
  •   Test script
  •   Environment variables
  •   Calendar script - Basic
  •   Calendar script - Basic - From Field
  •   Set statement
  •   Let Statement
  •   Script Header Comment
  •   Script Section Comment

As always, full instructions and download links can be found on the QlikView for UltraEdit page. Alternatively you can find them on branch.qlik.com or on GitHub.
 
If you have any ideas for other code templates or find any problems, let me know by leaving a comment or raising an issue on GitHub.

QlikView for UltraEdit now on Branch and GitHub

I have now moved the QlikView language definition for UltraEdit to GitHub and have added a project for it on branch.qlik.com also. Unlike the language definition for Notepad++, I haven't made any changes to the UltraEdit version but am working on a couple of minor bugs so a new version should be available soon.

If anyone is a regular user of the UltraEdit version and would like to help test future versions then please get in touch using the Contact Me link at the top of the page.

Before the move I totted up the number of times both versions have been downloaded and am amazed at the result. A wopping 5,423 times! I'm glad there are lots of "Qlikers" out there making good use of it.

QlikView Notepad++ Language Definition v2.3

I've just released a new version of the QlikView Notepad++ Language Definition. Version 2.3 contains the following minor fixes/functionality:

• Solved an issue with correct highlighting of nested function calls. Added comma "," as an operator to correct issue with function names not being highlighted correctly when it immediately follows a comma inside another function call. 
• Added missing function tool tips. All functions should now provide both auto completion and tool tips.
• Removed the ability to fold IF statements because of an issue where Notepad++ tries to collapse if() functions which causes the collapsing to fail.  

Additionally, I have moved the project to GitHub and have added it to http://branch.qlik.com/ too. As always, head over to the Notepad++ Language Definition page (or Branch) for the download link and instructions.

Getting Started with Qlik Sense

Yer yer I know, its about time I put together my first post about Qlik Sense. Better late than never hey. As a starting point, I thought I'd post the below video "Qlik Sense Desktop - quick build tutorial" from Qlik. It is the best "get started guide" I've found so far. It walks you step-by-step through getting Qlik Sense installed to creating your first visualisations and answering business questions. And you don't need to be a coding whiz either, not a single line of script was hurt during the making of this video.

QViewer v2 Released

Some time ago now I reviewed QViewer, a great little tool that allows you to view the contents of a QVD without having to load it into a QlikView app. This can be a really useful thing to do when working with complex ETL solutions in QlikView. Since then it has become a firm favourite in my QlikView development toolbox.

Dmitry originally created QViewer as a tool for himself, only later releasing it to other QlikView developers. Because of it's humble origins, it was never the most polished of software tools, but the fact that it has been downloaded more than 10,000 times speaks for itself.

This week Dmitry has released a v2 of QViewer. As well as a new, cleaner user interface, the engine has been rewritten to give huge performance improvements. And best of all, if you already have a license for QViewer then you can download v2 for free here http://easyqlik.com/download.html.

Grab yourself some cheap QlikView ebooks

The guys over at Packt Publishing are marking their 10th birthday with a sale. You can grab any ebook or educational video on their site including their full array of 6 QlikView books for just $10 USD (about £5.90 GBP) each. Click the banner below to find out more.

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:

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.

Review of QlikView for Developers Cookbook

Packt Publishing have been slowly but surely securing themselves as the one stop publishing house for QlikView books. With no less than 6 titles now available and more in the pipeline, there can be no doubt they've been busy, but are they worth spending your hard earned cash on?

Some time ago I reviewed their first QlikView book QlikView 11 for Developers which was written by Miguel Garcia and Barry Harmsen and released back in November 2012. The subject of this review is the next book released, QlikView for Developers Cookbook written by Stephen Redmond. I confess I've had this book since it's release, but with a baby QlikView addict at home and endless work commitments, time really hasn't been on my side lately. And as with my previous book reviews, I like to ensure I've not just skimmed through the book but given it a thorough read. There isn't much point in me spouting opinion, and I certainly wouldn't recommend a book unless I honestly knew it's contents in detail. So here goes...

Dropping Tables using a Wildcard

Whilst working with a customer last week I had a conversation with one of their developers about dropping tables. The customer wanted to be able to drop all their temporary tables from their model at the end of the script. They used a naming convention in which their temp tables were always named ending in "-temp". Ideally we'd be able to do this using the inbuilt DROP TABLE statement in QlikView script, something like this:

DROP TABLES "*-temp";

Unfortunately this isn't supported and you must explicitly name each table in the DROP TABLE statement. I knew I'd solved this problem before, so after a little digging through old QlikView apps, I finally found the subroutine I'd written and I thought I'd share it with you all.

First of all you need to define the following subroutine at the start of your script. You can simply copy and paste it into your script or you can place it in a text file and include it in your script using the include statement.

SUB WildcardDropTables (vExpression)

    // Loop through the tables within the model
    FOR i = 0 TO noOfTables()-1 STEP 1
      
        // Get the current table name
        LET vCurrTable = tablename(i); // Get the current table name
  
        // If the table name matches the pattern then drop it
        IF wildmatch('$(vCurrTable)','$(vExpression)') THEN
            DROP Table [$(vCurrTable)];
            LET i = i - 1; // Needed as table index reduces once table is dropped
        END IF  
      
    NEXT

    // Clear the variables so they don't persist
    LET vExpression = null();
    LET vCurrTable = null();

END SUB

I've included some comments for those that want to know how it works so I won't bother trying to explain it here.

With the subroutine defined you can call it at any point after and as many times as you like. Calling it is simple as follows:

CALL WildcardDropTables ('*-temp')

If you want to know what is valid to use within the passed pattern, look up the wildmatch() function in the QlikView help.

Now before the best practice police hang me from the rafters, it is indeed best practice to drop a temporary table as soon as is possible within the script to free up the memory it is using. But it's a nice trick and there are always situations where best practice can't be applied.

I've a similar subroutine to drop temporary fields which I'll share soon.

Theft

It saddens me greatly that I'm having to write this post, but here goes...

From time-to-time I receive requests from other websites and authors for permission to reproduce parts of my blog posts. I am normally very flattered that people consider what I write is good enough to reference or reproduce, and until today, I have been more than happy to give approval to such requests. The only thing I have ever asked is that they make it clear what material they have reproduced and to cite the source when doing so.

Unfortunately, I have today come across another blog which has copied images and entire posts word-for-word from QlikViewAddict.com. After taking some legal advice I have sent  the owner of the site a copyright infringement notice. This time I am far from flattered!!!

I am also saddened to say that I recognise material from other prominent QlikView blogs too and so if you are the author of a QV blog, please drop me a message so I can send a link to the offending site. I refuse to advertise it here.

For anyone that wants to use any material from this site, unless otherwise stated, I am the sole author and copyright owner of all material published on QlikViewAddict.com and I politely request that you contact me and ask before doing so. You'll find a big "Contact Me" button at the top of every page of the site and I don't bite (well only at the weekends). I receive a lot of messages via this site and I try to reply to as many as I can. Unfortunately, due to having a family and paying customers to keep happy, I don't get to answer them all but I do my best.

QlikView Functions: autonumber()

Having our new baby (AKA the mini QlikView addict) around has meant very little time for anything, let alone blogging. So in order to ensure I at least manage the odd post or 2 I thought it would be good to start a new series of short posts on different qlikview functions and their uses. To kick things off I have decided to take a look at the autonumber() function and the closely related autonumberhash128() and autonumberhash256(). All 3 functions do a very similar thing so let's look at autonumber() first and then consider how the other 2 functions differ.

Autonumber() can be considered a lookup function. It takes a passed expression and looks up the value in a lookup table. If the expression value isn't found then it is added to the table and assigned an integer value which is returned. If the expression value is found then it returns the integer value that is assigned against it. Simply put, autonumber() converts each unique expression value into a unique integer value.

Autonumber() is only useful within the QlikView script and has the following syntax:

autonumber(expression [, index])

The passed expression can be any string, numeric value or most commonly a field within a loaded table. The passed index is optional and can again be any string or numeric value. For each distinct value within the passed index, QlikView will create a separate lookup table and so the same passed expression values will result in a different returned integer if a different index is specified.

So how exactly are the 3 autonumber functions different? Autonumber() stores the expression value in its lookup table whereas autonumberhash128() stores just the 128bit hash value of the expression value. I'm sure you can guess therefore, autonumberhash256() stores the 256bit hash value expression value.

Why on earth would I want to use any of these functions? Well the answer is quite simply for efficiency. Key fields between two or more tables in QlikView are most efficient if they contain only consecutive integer values starting from 0. All 3 of the autonumber functions allow you to convert any data value and type into a unique integer value and so using it for key fields allow you to maintain optimum efficiency within your data model. 

A final word of warning. All 3 of the autonumber functions have one pitfall, the lookup table(s) exist only whilst the current script execution is active. After the script completes, the lookup table is destroyed and so the same expression value may be assigned different integer values in different script executions. This means that the autonumber functions can't be used for key fields within incremental loads.