Qlik Best Practice – Scripting In Qlik Sense

In the latest of our Qlik Best Practice series we take a closer look at scripting within Qlik Sense.

There can be huge variations in the way code is compiled but when it comes to Qlik Sense, it’s recommended you follow these guidelines.

SCRIPTING IN QLIK SENSE

• Code comments should be added to the sections of the script where transformations of any kind have taken place to understand why it has be scripted in that manner. That is not to say you write a comment for each line of code!For example:

• Script should be split into sections based on a single internal table or outputted QVD, and named accordingly. Put differently, a section of script should consist of a single table’s transformations (where possible) – do not have all script on a single tab. This makes the code more readable.
• Leave the Main section for app configuration variables and other global variables that will be used in the app. For a similar reason to the point above, avoid scripting on this sheet. This allows developers to name the section intuitively, as well as containerise chunks of script appropriately.
• ETL applications should set ‘CreateSearchIndex’ to zero in the main section. This prevents the search index from being created – improving reload times. Search indexes are not required when the application is not used for analysis.
• Add a time-aware calendar, see OmetisToolkit.com’s Time Aware Calendar tool. It is common for users to want to filter based on weekday, date, week, month, quarter, year and combinations of the aforementioned. Equally, we often recommend providing report descriptions, enabling users to filter off frequently reported date ranges. For example, ‘Rolling 12 months’, ‘Current year’, ‘Last year’, ‘Rolling 4 weeks’, etc. We recommend using the Ometis Toolkit calendar or similar to leverage the full potential from Qlik.
• Avoid WHERE conditions on QVD loads. WHERE conditions knock-off the optimised load (this does not include WHERE NOT EXISTS). Where possible, and practical, a separate limited QVD should be created with the limited data set or used other methods of limitation such as a left/inner join to another table or using WHERE EXISTS.
• Avoid hard-coded values used in variables, data connections, etc. When you are required to reference a value, such as a date, make them dynamic/calculated where possible to reduce manual intervention and improve the overall robustness of an application. For example, the TODAY() function could be used to calculate a date value.
• Keep your scripts consistent, especially when working in teams. This will improve code maintainability. Therefore, consider and agree a standard for adding comments, code indents, naming conventions, use of apostrophes/square brackets for fields and tables with spaces, script layout, use of case (i.e. camel case), etc.
• Use Qlik script syntax only and avoid third-party libraries/include statements unless they offer a significant improvement in performance of the application or development time. External code can cause a black-box effect, where developers lack a clear understanding of the what the external code is doing. Equally, maintaining script outside of Qlik can become a headache.
• When using SQL queries, consider using (WITH NO LOCK), this prevents the query waiting on locked records, instead skipping over them and ultimately speeding up reloads. These records could be captured on the next reload. Not using (WITH NO LOCK) could result in reloads taking considerably longer, and possibly timing out.
• Hide fields that aren’t applicable for analysis, such as compound fields that are only used for data modelling. This can be achieved through using hidePrefix/hideSuffix. The benefit of hiding irrelevant fields is that it reduces the number of fields from the field list, and thus enables users to see the wood for the trees.
• Equally, you should exclude fields that aren’t applicable for filtering from the smart search, this is achieved via the EXCLUDE statement. The benefit of excluding irrelevant fields is that it reduces the number of fields to search on and removes inappropriate search results. Thus, providing more meaningful results.
• Where possible you should tag fields. Tagging geometry and datetime fields can significantly improve usability/self-service.
• The App configuration (Main section) is setup correctly and consistently between applications. Configuring the main app variables improve the efficiency and consistency of developers. This includes setting date, time, numerical abbreviations and money formats, as well as setting variables to control the first day of the week and whether to index the fields on reload.

By following these practices, and standardising your applications, you should find it simplifies the maintenance of your existing and future applications. Therefore, enabling any developer in the team to jump into any application and understand the inner workings of it with ease. We believe the list above maintains a good balance of guidelines to follow without being too restrictive that it becomes a burden to stick to.

Don’t forget to stay up-to-date with all the latest Qlik tips and tricks by following us on LinkedIn, Twitter and YouTube.

By Chris Lofthouse