Error Handling in Qlikview

Recently, one of our customers came up with this requirement: Create a QVD with an incremental load.  But, then there came a problem-”How would I do an increment load on a QVD, when it doesn’t exist in the first place? “ (Wait, it will be confusing, if you don’t know about incremental load, check it here, http://www.learnallbi.com/incremental-load-in-qlikview-part1/ ) then I thought, if at all I could trap those errors and use them for my convenience, I’d be able to do this and there began my search for error handling in Qlikview. 

 

To my luck, Qlikview did mention about it in their manual, not very detailed though. So I thought I would give head start to those who would be trying to do their own error handling in Qlikview like me.

 

My program is simple, I need to do an incremental load on a QVD (Let’s call it Stage 2 QVD), which stores snapshots of an existing data (Stage 1 QVD). So, I don’t need to use any unique identifiers for this type of incremental load, just concatenate new data with a new date-stamp and store it away. 

 

So, first I will check whether Stage 2 QVD exists? If not, create it with first set of data. Oh, it’s already there? Then go take data from it and add new set of data from Stage 1 QVD, to it. There you go, quite simple! 

 

So, now how would I handle that error, if that Stage 2 QVD is not there? Don’t freak out! Qlikview has it covered for us.  First set ErrorMode to 0 in the script,

 

Set ErrorMode=0;

clip_image001[12]

This is manual override for errors in Qlikview. Careful, now though, you wouldn’t be able to see any errors in your application from this moment on. Next as our requirement is to find whether Stage 2 QVD exists or not? for this we will write a simple IF ELSE statement.

 

So, IF ScriptError=8 or ScriptError=10, here ScriptError=8 is “File not Found” and ScriptError=10 is “Table Not Found”, So if either of these errors has occurred then create a Stage 2 QVD.

 

clip_image002[12]

ELSEIF ScriptError=0 ( I have used 0 as No Error, as when no errors are there ScriptError will stay 0. It will only become 1 after finishing first statement execution or finishing the script ) which means if QVD exists then take data from Stage 2 QVD and append new data from Stage 1 QVD and store the same into that Stage 2 QVD which would replace existing QVD.

clip_image003[14]

If any other error than mentioned occurs in the script, it will exit from the load.

clip_image004[15]

 

Refer to Qlikview 11 reference manual for more detailed execution of these. I will quote error handling section mentioned in reference manual for your convenience here.

 

ErrorMode:

This variable determines what action is to be taken by QlikView when an error is encountered during script execution. By default (ErrorMode=1) the script execution will halt and the user will be prompted for action (non-batch mode). By setting ErrorMode =0 QlikView will simply ignore the failure and continue script execution at the next script statement. By setting ErrorMode =2  QlikView will trigger an “Execution of script failed…” error message immediately on failure, without prompting the user for action beforehand.

 

Example:

set ErrorMode=0;

 

ScriptError:

Returns the error code of the last executed script statement. This variable will be reset to 0 after each successfully executed script statement. If an error occurs it will be set to an internal QlikView error code. Error codes are dual values with a numeric and a text component. The following error codes exist:

 

1 No error

2 General Error

3 Syntax Error

4 General ODBC Error

5 General OLE DB Error

6 General XML Error

7 General HTML Error

8 File Not Found

9 Database Not Found

10 Table Not Found

11 Field Not Found

12 File Has Wrong Format

 

Example:

set ErrorMode=0;

load * from abc.qvw;

if ScriptError=8 then

exit script;

//no file;

end if

 

ScriptErrorDetails:

Returns a more detailed error description for some of the error codes above. Most importantly this variable will contain the error message returned by ODBC and OLE DB drivers for error codes 3 and 4.

ScriptErrorCount:

Returns the total number of statements that have caused errors during the current script execution. This variable is always reset to 0 at the start of script execution.

ScriptErrorList:

This variable will contain a concatenated list of all script errors that have occurred during the last script execution. Each error is separated by a line feed.

 

References : Qlikview Reference Manual v11

 

 

Qlik and Tableau – Mobility

Remember those times where phone used to be just a phone, all you can do is talk and carrying it around was a bit of task for us back then. Over a short period of time all this has changed and our phone has become a medium for everything, your email, organizer, web, music, movies, podcasts, weather, dining and travel as well. These days everyone is so concerned about mobility whatever they can do on a computer, they want on their mobile phone as well.

Business Intelligence is not an exception in this subject; we don’t want to open our laptops everywhere just to make a small analysis. So, for this lot of vendors in Business Intelligence market have expanded their technology from being able to work on a computer to a mobile phone or tablet. If you are into Business Intelligence and reading this, I don’t have to explain, how convenient it would be to make analysis of your yearly sales data from your phone sitting in a train on your way to work.

Let’s see how our favorite tools in BI – Qlikview and Tableau fair on a mobile platform:

Category

Qlik

Score

Tableau

Score

User Interface

Qlikview user interface can be designed as per your business requirement. If the objects are bigger then it will be better to use on a Mobile device. You can’t expect your regular “ Dimensions on the left, Graph on top and Table below ” kind of dashboard to be responsive on the mobile device

    4

Same as Qlikview, UI can be designed as per your requirement. But, Tableau has an advantage of having bigger objects be allocated in the given screen space, giving an appeal to the user and much more space and responsiveness for clicks.

   5

Ease of Use

Qlikview gets you to the point where you want to see the data for. But, It’s a bit daunting task – clearing selections and selecting a particular quarter data by pinching and panning, didn’t respond that quick, have to fiddle around a bit to get around. May be, this could be because the dashboard I have used isn’t entirely designed for Mobile devices or my fingers are big. But, wait the dashboard which you can use on your computer should be accessible with same ease on your mobile devices as well, isn’t that the whole point of mobility in BI?

    3

Tableau is much easier when it comes to selections on a dashboard as it responds to your actions quickly and using it for analysis will be easier compared to Qlikview. I also have faced similar problems like freezing at times or slow response to touches, this is an issue of size of the dashboard and complexity in it.

   4

Deployment

Qlikview Deployment can only be done through a licensed Qlikview Server, a purchase is necessary in order to get your data on your mobile phone. Putting that aside, Qlikview deployment is easy and can be accessed through that secure private link of your server on your mobile phone.

    4

Tableau has both licensed server version for secure access and also Tableau Public which allows you to upload things on the server but it has no control over who can access it. It is available for all who can access Tableau Public. Then we have Tableau Online, cloud based version of Tableau server, all data sources you want to connect will be saved on the cloud and Dashboards can be shared across with no need of behind the firewall or within VPN access.

    5

Collaboration

I haven’t really tried this feature, So I’m not going to score them here. But, from what I have read, Qlikview has collaborative features like people can share a dashboard and changes made by one person will be shared across people who are viewing that dashboard at that same time, just like a WebEx session.

Tableau also has more or less same collaborative features as Qlikview. But, it also has an embed feature, where dashboards can be integrated into your webpage or blog.

Qlikview and Tableau, both are still a bit rusty on Social network collaboration features.

 

Qlikview is going to roll out Qlikview>Next which is more advanced than what Qlikview has in terms of graphics, mobility and some advanced features. If it is going to be what it is as they are saying, Qlik.Next will give a tough competition to all BI tools in the market now.

Conclusion, Qlikview needs some improvement for its use on mobile devices. It could be because of the complexity of the dashboards. Tableau is faster and responsive compared to Qlikview owing to its bigger real estate or responsive objects.

 

Tableau’s Performance Recording

Tableau has yet another unique feature,  which made me jump and post about it here.

We all have been in a situation where we had to find out that single query, report or code where it is costing us too much on performance of our dashboard. So, we had to dig deep into the dashboard and find which one is putting that extra burden on our dashboard, making it run like a 20 year old truck.  But, have you ever wondered what it would be like if you could publish a dashboard out of it, to see performance. wouldn’t that be easy to analyze things?

Yes. Tableau has this integrated feature called Performance Recording. All you need to do is push start, keep working on your dashboard and push stop again and it will give you out a new dashboard with Performance Summary of your dashboard.

Let’s get into details: Open your Tableau Desktop and open your dashboard/workbook which you want to check performance for and go to Help > Settings and Performance > Start Performance Recording , which will start recording performance of your dashboard with each event you make on the dashboard, let that be moving from one sheet to another, opening a dashboard, making selections. All events which are made after you Start Performance Recording, will be recorded and once you are done, go to Help > Settings and Performance and click Stop Performance Recording.

There you go! Your Performance Summary will pop open as dashboard giving out details like Events, Timeline, Query.

image

Timeline:

image

Events:

image

I have used Tableau Desktop version (8.1) here. I will also share my thoughts on server version, as soon as I could lay my hands on it.

Qlikview vs. Tableau

We have been working on Qlikview and Tableau for a while and we have decided to compare these two tools, which have evolved as market leaders in Business Intelligence. Both have their pros and cons and finally its you and your business requirement, which may narrow it down to one tool. I’ll put down those differences which I have found so far while working on both.

Here are the differences which we have found between these two BI giants in the market. In this comparison below, we have also rated each tool based on categories development, deployment, user interface (UI) and storage,  on a scale of 1-5 along with description of differences. Where 1 being poor and 5 is excellent.

  # Index Attribute Type Qlikview Score Tableau Score

1

Data storage for optimized loads

Storage

QVD

5

TBE, TBEX with all data

5

2

Direct load from Database (Any denormalized/staging or direct database)

Development

Scripting and Manipulation adding logic

5

Custom SQL option, adding Excel or Tableau sources can only have joins or restrict data using filters.

3

3

Coding standards

Development

Almost similar to SQL – that is Qlikview scripting

5

Custom SQL function while connecting database can be used, But, this has its limitations. (i.e. Certain syntax and aliases has to be used , normal SQL can fail at times)

3

4

Joining tables at the load from database or external data source

Development

Direct code in the scripting

5

Add first table then add second table and use define joins at that stage

3

5

Objects in Sheets

UI

Sheet can be made as Dashboard by adding in Objects or all real estate can be used for one object

4

Sheets can have only one object, for keeping more than one Object, Dashboard option has to be selected

5

6

Graphical inputs and Changes by data (Colors of Bars, Size, Labels)

UI

Logic has to be written in specific areas where graphical changes has to happen based on data

2

Just drag and drop columns and data will be sorted based on it.

5

7

Adding different data sources and Joins

Development

Qlikview makes automatic joins between tables.

4

Need to establish a relationship between tables, though it establishes relationship between two tables on same column names, We need to edit them for more refined execution and while creating dashboards link should be established between two data sources, for using both in one dashboard.

4

8

Flexibility of using multiple data sources in Dashboards

UI

Qlikview can handle multiple data sources effectively without much work arounds

5

Tableau has limited functions while handling multiple data sources. But It has direct click access to more data sources . Example:  Actions (Triggers) have limited functionality for applying on multiple data sources.

4

9

Creating data sources –  (Format structure)

Storage

Qlikview creates data sources in stored xml file format with an extension – QVD for faster execution.

5

Tableau also stores data sources pulled from database in stored xml file format with an extension – TBE

5

10

Extracting multiple tables from Databases

Development

Qlikview script can pull data from multiple tables and joins will be established between them based on common columns. (Primary Keys and Foreign Keys).  This has its limitations.  But this is faster and easier compared to Tableau and this is based on scripting.

4

If multiple tables should be extracted from same data source, they should be linked to first table which was extracted and each table which has to be extracted should be added to the first and all of them will be extracted as one big Data Source. Tableau does this to avoid performance issues, even when we try to link each of them after extract, It will prompt to do the first process. This is just click and add process, no script or code involved in this processs.

3

11

Establishing relation between data sources.

Development

Without defining proper relationship (that is putting out columns with same name for Joins) between tables may result in Synthetic keys (Joining between common columns) and again, that would make developer’s work more time consuming.

4

If multiple tables are extracted from same data source without establishing relation between them at the load time. May result in performance issues and becomes developer’s nightmare. As listed in the earlier point, It will prompt for establishing relation while extracting as it causes performance issues.

3

12

Data analysis in charts

UI

Qlikview has complex analysis features like Accumulation, Running Total and Forecast. But these options should be defined using proper functions requires coding

3

Tableau has these features based on click and drop basis and adding function name directly in the calculated expressions feature.

5

13

Geographic  Maps

UI

Qlikview needs scripting and export an extension for this and we need to generate a Longitude and latitude file for the proper map fuctionality

1

Tableau has inbuilt extension for Maps and all we need to have in data is Country and City information.

5

14

Server 

Deployment

Qlikview Server is a propreitary version and not available for public use.

2

Tableau has a version which is Tableau Public, which can be used by registered users, which is free and dashboards can be uploaded and shared. But, this is a public version. Privacy is not ensured. However, they have a leasing out option for corporates for that purpose.

4

 

So, finally here are the total scores, when it comes down to each category.

Qlikview and Tableau scores:

Qlikview and Tableau scores.

Comparison by their scores in each category: Tableau vs. Qlikview by Category

 
So finally, Qlikview scored more on the development and more or same on the storage and on other areas Tableau has proven to be stronger, because more of its ease of use, advanced UI and Tableau public.
 

Personally, I’d use more complex and complicated dashboards on Qlikview, due to its scripting abilities, where I can write complex logic and retrieve data from database without much struggle. Where as, Tableau for already manipulated and saturated data from database without much complicated logic needed to be built on Tableau while retrieving from a database and for a better UI and better geographic maps.

Yes, its debatable. You may end up saying I can do what all you can on Qlikview, on Tableau as well and yes, you can. But, the effort it takes to build that same application on Qlikview may take much more effort on Tableau and vice-versa the UI which can be developed in Tableau may take more effort in Qlikview than earlier.

At the end, We love them both and we love working on them.

World of Business Intelligence

Lately, We have started working on Qlikview and Tableau. We have also started exploring tools like Microstrategy, Tibco Spotfire, IBM Cognos and others. There will be an immense demand of efficient Business Intelligence tools in near future due to growing data all over the world and We will try to explore the options we can in this world of Business Intelligence and we are going to share our experiences on it, with details on each tool we have come across.

So keep following this blog, we won’t let you down.