Have you ever experienced the following situation? A challenging problem presents itself. After many hours of thinking, developing, thinking some more, and developing some more, you have crafted a solution. "This is brilliant work!" you say to yourself, "It completely solves the problem, and in a very elegant way too."
Fast-forward a few months. A new business question presents itself and a small change to your original solution is required. You look at your original work, and after some poking around decide that you can make neither head nor tail of it."This is horrible work!" you say to yourself, "What was I thinking at the time?".
Did your script really go from brilliant to rubbish over the course of 6 months? Most likely not. You have just lost familiarity with the script. Fortunately, there are ways to ensure that you (and others) are able to quickly get up to speed when modifying existing QlikView script. The secrets are organizing your scripts and using naming conventions.
As we saw when we first looked at the script editor, the script can be split up into different tabs. It is advisable to divide your script into different tabs, each one focusing on a different functional area or table.
To add a tab, select Tab | Add Tab from the menu or click the Add new tab button on the toolbar. Tabs can be moved left and right by selecting Tab | Promote and Tab | Demote respectively, or by clicking the corresponding buttons on the toolbar.
Let's organize our script by opening the script editor and following these steps:
Now the script is starting to look organized.
Comments can be added in the script in two ways. A single line can be assigned as a comment by prefixing it with //. For example:// This is a single line comment
Additionally, multiple lines can be converted to comments by enclosing them between /* and */. Like this:/* This is the first line of the comment This is the second line of the comment*/
It is advisable to comment the following things:
An example of comments added to the Aircraft Types tab of our document is shown in the following image.
Adding an information tab
It is good practice to add an Information tab to your script. On this tab you document, amongst other things, information about who developed the document, what the goal of the document is, and when it was last modified. Additionally, a change log can be included to track which changes were made over time.
Take a moment to add an information tab to your document. An example template is shown in the following screenshot:
Besides tabs, comments, and an information sheet, using a proper layout for your script greatly increases readability. It is recommended to use indentation to visualize the different levels in your script. It is also recommended to align all of your aliases (the field name after the as part in load statements). Compare the following script to the commented script shown earlier and you will notice that it is much easier to read.
Lastly, it is recommended to use naming conventions and to use these consistently throughout your script. We will now have a look at the naming convention that is being used for the documents in this book.
Table naming conventions
Tables that will be used in the final data model have a "business" name that is in plural. That means, a business user understands what is stored in the table. So instead of naming our table cst_data, we name it Customers. This also means that it is permissible to have spaces in our table names.
Mapping tables are prefixed with the word Map so that they are immediately recognizable as mapping tables. These tables can use technical names, for example,
Temporary tables are tables that are not used in the final data model, they hold a temporary or intermediate result. We did not yet use any temporary tables in our examples, but when we use them they are prefixed with TEMP. For example, Temp_Flights.
Field naming conventions
Like tables that are used in the final data model, field names also have a business-friendly name. For example, Aircraft Name instead of ssd_name.
As many of these names contain spaces, field names are enclosed in square brackets by default, even if they do not contain any spaces.
Key fields, fields that are used to link tables together, are prefixed with a % (percentage) sign. For example, [%Aircraft Type id].
Key fields can cause confusion in the QlikView frontend.As these fields are used in multiple tables, they can return unexpected results when used in an aggregation function. It is therefore advisable to hide these fields from the frontend view.
There are two variables that can be used to hide fields:
HidePrefixand HideSuffix. The first variable hides all field names that start with a specific text string and the second one hides all field names that end with a specific text string.
To hide our key fields, we can add the following statement at the start of our script: SET HidePrefix='%';
Measures, fields that contain amounts, are prefixed with a # (pound or hash) sign. For example, [# Total Passengers].
Flags, fields that contain a Yes/No or 1/0 indicator, are prefixed with a _ (underscore) sign. For example, [_Flight arrived on time].
Qlik View Related Interview Questions
|Microstrategy Interview Questions||IBM Cognos Interview Questions|
|PL/SQL Interview Questions||MSBI Interview Questions|
|VBA For Excel Interview Questions||SAP BO Interview Questions|
|SQL Database Interview Questions||Qlik View Interview Questions|
|R Programming language Interview Questions||Pentaho Interview Questions|
|Advanced SAS Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.