QVS file - Reusable Qlikview Scripts

qvs (QlikView Script) files allows  developers to store QlikView load scripts into a external script file. The external script (qvs) file can be referred in multiple qvws.  Usually, script file with QlikView Script code is saved with .qvs extension. However, the file extension does not matter.

It always a good practice to store common script code into qvs files and include the script files in required qvws.
e.g: Calendar table code is often same in most of the Qlikview dashboards. Instead of replicating the same script in each and every dashboard, the Calendar table script can be stored into a qvs file and same can be referenced in required Qlikview applications.
This practice removes duplicating the same code in multiple applications and makes the maintenance easier.  Any update to code are made in single place eliminating redundant effort. Also, Qlikview application script now looks cleaner.

include or must_include statement is used to refer the external qvs files in Qlikview applications.
e.g:

 $(include=load_script.qvs);
 $(must_include=load_script.qvs);



While script execution, Qlikview will replace the include statements with the code from the external qvs file.

While INCLUDE and MUST_INCLUDE serve the same functionality the major difference is must_include aborts the execution if the Qlikview cannot locate and load the external script file, while "include" statement skips the statement and moves onto next statement.

I usually use Notepad++ to edit the QVS files. Since, qlikviewaddict has developed custom language definitions for Notepad++ editor for Qlikview scripts with syntax highlight, auto-completion and tool tips feature. Refer my post for more details.

CSV Reports from Qlikview Dashboards

I recently came across a requirement where in the client wanted us to automate daily email delivery of a csv/excel file containing data of a straight table from one of the Qlikview app. Obviously, Qlikview publisher has the capability to create and email pdf reports from Qlikview apps, but it does not have options to create csv/excel reports. We started evaluating multiple options to achieve the requirement.

We coded a macro to export the table into excel file and created a trigger to execute macro code on Post Reload of the app and later use a batch file to email the excel file using sendemail.exe .

The trigger appears to work when the app is refreshed manually through Qlikview desktop. However, when the refresh is triggered through publisher tasks, the trigger doesn't work.

After trying many unsuccessful workarounds, we decided its time think other ways to achieve the requirement.
One of the idea that came from my colleague is to create the csv file using load script in the Qlikview app. That sounds simple and does not require any complex macro script and triggers!!

We modified the load script to create a Qlikview table in the app with data required for reporting. Then added store command to save the file with .csv extension on the server.

We created tasks on publisher to reload the app and while reloading the app , it creates a csv file on the server. We created batch file for emailing the .csv report to user and set up the dependency on the app reload and email script using enterprise scheduling tool like OpsWise or Control-M.

The method has its limitation but works like charm for simple requirements. Feel free to comment any suggestions to improve the process or any other ways to achieve it.
There are third-party tools like NPrinting which enables rich and advanced reporting features with excel and many other formats. But, comes with a cost.

Auto Generate Sample Scripts

Qlikview provides a sample script, that can be used to create several dummy tables and test app.
This feature is useful when you are planning to practice creating Qlikview charts, tables and other visualization objects and you just need some sample data to start with.

To use the Qlikview provided sample script,  follow below simple steps.

1) Go to File --> Script Editor  or Press Ctrl + E
2) Click on Insert  ->  Test Script or Press Ctrl + Q twice

This will insert sample script in script editor. Save the app and reload to run the script.
Once the script excution is Click on File --> Table Viewer or Press Ctrl + T to open the Qlikview data model.

Now the script and sample data is ready, you can start creating charts and tables for the app.


Please keep in mind that the script generated by Qlikview heavily relies on rand() function for generating data and does not use any other source.

Top N in Qlikview Straight/Pivot Tables and Charts

Often we want to display Top N values in Qlikview straight/pivot tables and charts. This requiers restricting dimension in the chart/table to display Top N records based on the metric.
We can use aggr( ), rank() and sum() functions together in dimension to achieve the same.

For example, Create a Pivot table to display Gross Revenue and Gross Room Nights for Top N (top 10 in this example) Hotel Markets based Gross Room Nights.

Right on the sheet and Add New Sheet Object --> Chart
Select Straight Table and click Next button.

Dimension: Click on Add Calculated Dimension and add the expression
aggr(if(rank(sum([Gross Room Nights]))<=10,[Hotel Market]),[Hotel Market])

This will limit Hotel Market to Top 10.




Displays Top 10 Markets based on the gross room nights metric. Remaining markets are displayed in single record as null with '-' symbol in the table.
You can suppress the null record by Clicking on the dimension and selecting Supress When Value Is Null option on Dimensions tab

Label the calculated dimension as Hotel Market.

Click on Next button and add following expressions and label them appropriately.

Expression 1 : sum([Gross Room Nights])
Expression 2 : sum([Gross Revenue])

Click on Finish button.

Table now displays Top 10 markets and Gross Room Nights and Gross Revenue for the markets.

Hope this example helps!

Note: Above expression can be sightly altered to display Top 10 Hotel Markets and everything else labeled as 'Others'.
aggr(if(rank(sum([Gross Room Nights]))<=10,[Hotel Market],'Others'),[Hotel Market])

How To Install Netflix in Ubuntu 12.10

Until recently, watching Netflix on Linux was not possible since Silverlight was not available on Linux. Finally, Netflix is available in Ubuntu 12.10 in the form an unofficial app. I have used it and it works great.
Installing Netflix on Ubuntu 12.10 is quite simple.

Here are the steps:

1. Open up a terminal window
2. Type sudo apt-add-repository ppa:ehoover/compholio and hit Enter
3. Issue the command sudo apt-get update
4. Issue the command sudo apt-get install netflix-desktop
5. Say OK to dependencies and Installation will begin.

Depending on the network speed, this could take some time. Once that’s over.

1. Open the Unity Dash
2. Type netflix  and NetFlix app will appear.
3. Click on the Icon and Enjoy your movies!

Note this is a work around and  not an official app from Netflix and is not supported from Netflix.

Line Chart Trick to Avoid Horizontal Scroll Bar

Often line chart in Qlikview apps  are displayed partially due to space limit on the sheet. When the chart is stretched the the lines starts appearing completely.
To make the lines on the chart appear completely either chart needs to stretched or chart properties needs to updated to have horizontal scroll bar (presentation tab).
Horizontal scrolls works most of the time but they are annoying sometimes.
Do you want to display the line on the chart completely without stretching or scroll bars?
Follow the simple trick to cheat Qlikview to make it display complete chart without horizontal scroll bars.

1) Right  click on the line chart and open the Properties.
2) In General , select Bar Chart as Chart Type
3) In Presentation tab,  select"Show All Bars" check box.
4) Now, go back to General tab and select Line Chart as Chart Type.
5) Click OK

Line chart now displays complete line and horizontal scroll bar is suppressed.

Hope this helps!

Alternative to Count Distinct

Count distinct "Count( Distinct <Field Name>)" is an expensive expresssion in Qlikview. Whenever used, it turns Qlikview to use single core instead of multi-cores resulting in slow performance of the app.
By making a small change in data model we can replace COUNT DISSTINCT with SUM function and improve the app performance marginally and increase app response time to users actions.
To achive this add another field in Qlikview table having value 1 along with the field you need to count.

Example:

Bookings:
LOAD
    [Member Id],
    [Booked Date],
    [Transaction Id],
    [Booking Amount]
FROM Bookings.qvd (qvd);

"Member Count":
LOAD
    [Member Id],
    1 AS "Unique Members"
Resident Bookings;

To count distinct members
COUNT(DISTINCT [Member Id]) can be replaced with SUM([Unique Members]).