Qlikview SUB Optional Arguments – Can Subroutines have optional parameters?

Indeed! A Qlikview sub can have optional parameters.  The QlikView 11.20 SR15 documentation specifies:

If a subroutine has more formal parameters than actual parameters passed by a call statement, the extra parameters will be initialized to NULL and can be used as local variables within the subroutine.

A subroutine can have no arguments and it is specified without parenthesis as seen in the example below.

If a subroutine has one parameter it is defined within parenthesis after the sub declaration.  To call a subroutine that defines a parameter without passing in an argument then the call statement does not use a parenthesis.

To pass the parameter the sub call needs parenthesis and the arguments are passed as strings.

A subroutine with two parameters can be called with the second parameter optional.

In a previous blog post, Drop Fields only if they exist in Qlikview, I demonstrated a subroutine that would drop a field after checking it existed in the data model.  Its purpose is to prevent reload errors when dropping fields if the names change that prevent a successful reload. It also logs the missing field via a trace statement.

In the DropFieldExist subroutine below there are two parameters.  The first, required, is the field name to drop.  The second, optional parameter, is the table from which to drop the field.  Additionally, the subroutine verifies that the field and table exist before attempting to drop them thus preventing an error in the reload process.

Drop Fields only if they exist in Qlikview

One issue I find working with the QlikView data model is the load script failing due to changing the name of a column that is eventually dropped in the script.

Sometimes the data model contains a lot of fields that are not used by a specific QlikView application so the application developer drops the fields to improve performance. However, this causes a problem when these fields are changed.

To reduce the impact on scheduled reloads I developed a subroutine to check the field exists before dropping it. I call this subroutine DropFieldExist.

The only parameter is the column name to drop.

// This Sub will check if a field exists before dropping it
// Use to avoid errors during script load when dropping fields due to name changes;
// Example:
// if call QVSDeveloper.DropFieldExist('MyColumnName')

SUB QVSDeveloper.DropFieldExist (vL._Root)
 IF (NOT ISNULL(FIELDVALUECOUNT('$(vL._Root)'))) THEN
 DROP FIELD '$(vL._Root)';
 ELSE 
 TRACE '$(vL._Root) Field Not Found';
 ENDIF
 SET vL._Root=;
END SUB;


// **********************************
// Application written by Andy Ritting 
// qvsdeveloper.com
// **********************************

I recommend implementing this script using the QDF Framework.  I put it in a folder 3.Include\6.Custom\3.4.Sub and use a Must_Include statement to use it.

$(Must_Include=$(vG.CustomPath)3.4.Sub\QVSDeveloper.DropFieldExist.qvs);

Many thanks to Marcel Hug and the Qlik Community for helping to develop this subroutine.

 

QlikView Development Tools: What I use

  • The Qlik Scripting Language
  • Qlik Deployment Framework (QDF).  A repeatable organization of Qlik programs, folders and variables downloaded from the community.qlik.com customer community website.
  • Sublime Text 3 –  a text editor to create Load script saved in qvs files with an add-in to format Qlikview statements in different colors
  • EasyQlik Viewer – a program that opens QVD files and can filter the data ( like excel data filtering) and provides metadata about the QVD file ( column names, source, data types)
  • Git – an open source control system that works on the client.   I use Git to move Script changes from my PC, to the bitbucket server, then from bitbucket to the dev Qlik publisher and then production.  This is done with a development and master branch
  • UltraCompare  – a folder/file comparison tool for moving other changes between my PC, dev and a production
  • Jira/ bitbucket – a project management and git source control system server
  • Windows Server 2012 R2 Remote Desktop Server.  I publish the QlikView desktop app from this so I can run reloads in design mode directly on the Dev server to resolve PC /ODBC setup conflicts.
  • SQL – IBM DB2, SQL Server T-SQL, MySql languages
  • IBM Client Access – required for data ODBC drivers and data transfer tools
  • Squirrel SQL – a JDBC SQL Client editor that I prefer over the Run SQL Statement program provided for in IBM Client Access
  • Preferred Strategy Quick launch business views.  These are SQL Views that transform JDE data into business readable column names and data types.
  • Excel – Excel is the source for some data
  • SQL Server Management Studio.  SQL Server is the source for some data (data warehouse)
  • MySQL workbench tools
  • Powershell – Powershell scripts do some automation for me like the creation of QVW generators

Why you must include the Must_Include variable in your QlikView Scripts

The Include and Must_Include QlikView system variables take text from a file outside of the QlikView application and inserts the contents of the file at the location of the statement. The Must_Include variable in a statement that includes your custom QVS script is essential in moving from in-app QlikView script development to reaping the benefits of using a text editor for script development. Continue reading “Why you must include the Must_Include variable in your QlikView Scripts”

QlikView PDF Reference Manual

While developing QlikView applications the most often referenced help commands are the F1 command for in-application help and Internet search that results in community.qlik.com or other Qlik related blogs.

However, for strategic and directional thinking there is an authoritative reference guide for lurking on your PC’s hard drive in the form of the “QlikView Reference Manual.pdf”. Continue reading “QlikView PDF Reference Manual”

Use Robocopy for Qlik Deployment Framework (QDF) DTAP process

QlikView as a programming environment is similar to Excel, meaning there is absolutely no structure provided natively. Every QVW file application has code and data. If you copy the file you now have two versions of the code and data… Much effort as a lone developer has to go into corralling and organizing files and folders and their relationships between each other. Eventually a Qlik developer will come up with some system of files and folders to Continue reading “Use Robocopy for Qlik Deployment Framework (QDF) DTAP process”

Hello World

I was listening to an episode of the Talk Python To Me podcast on which their guest John Somnez was speaking about the Soft skills of software programming. Well, I thought his message was inspiring enough to take note and look him up so I checked him out on his webcasts on youtube.  His site, simpleprogrammer.com, had a email correspondence course on setting up a blog, which you can find here.

It’s easy to read through his emails but doing the work takes time and effort. So I thank John for getting me started and for motivating me to give back to the Qlik developer community.

The challenge is to expand my thinking outside of my cubed world… so here I am to share my knowledge in my specialty of Qlik Script Development in the Qlik Deployment Framework to help my fellow Qlik developers. Thus far in my career, that started as a SQL Server DBA, I’ve been a jack of all trades straddling the fence of development and technology and striving to be an expert at everything. I’ve turned the Database Administrator acronym into Does ‘Bout Anything and while working at a small global company I’ve known no boundaries. At first I thought I would just get to know the operating system to be a better DBA.  When I thought my sphere of influence would stop at the Hypervisor, or the Ethernet port, to then designing our VMWare server architecture to helping my colleagues use our ERP system. At my core I’m a data guy that wants to be a developer (on great computer hardware) to help people with their data driven needs.

Hello World.