Standardizing and organizing script - Qlik View

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.

Using tabs

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:

  1. Select Tab | Add Tab from the menu.
  2. Name the tab Mapping and click on OK to close the Tab Rename window.
  3. Promote the Mapping tab so that it is in front of the Main Data tab.
  4. Move all MAPPING LOAD statements (Map_Manufacturer_Country, Map_ Table_Comments, and Map_Field_Comments) from the Aircrafts tab to the Mapping tab.
  5. Create a new tab by clicking on the Add new tab button on the toolbar.
  6. Name the tab Comments and click on OK to close the Tab Rename window.
  7. Promote the Comments tab so that it is in front of the Main Data tab.
  8. Move all COMMENT statements from the Aircrafts tab to the Comments tab.
  9. Save the document.

Now the script is starting to look organized.

Standardizing-and-organizing-script

Comments

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:

  • Table names: They makes it easy to understand which script belongs to which table
  • General information: For example, who made this change, when, and also which field is being used as a key field in a join statement
  • Business logic: It describes what the business logic is and why you are taking a certain approach

An example of comments added to the Aircraft Types tab of our document is shown in the following image.

Aircraft Types tab of our document

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:

Adding an information tab

Script layout

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.

STANDARDIZING AND ORGANIZING SCRIPT

Naming conventions

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,
Map_Manufacturer_Country.

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].

Hiding fields

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].

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Qlik View Topics