Blog

Qlik is one of the leading providers of business intelligence and data visualization solutions on the global market. More than 48,000 organizations use QlikView and Qlik Sense products – an impressive figure, but one that makes you think.

How many users from all these companies are really able to get the most out of QlikView and Qlik Sense capabilities?

We have prepared a few tips and tricks that will optimize your work when designing and developing applications based on QlikView and Qlik Sense. In general, they apply to both products, but some are only for QlikView. This will be indicated in each section. A number of tricks are exclusively technical, others will come in handy when creating any objects.

This article will be useful for a beginner, while a versed developer can test their knowledge.

1. Commenting in the script

Comments in scripts allow other people to understand what is going on. This does not mean you need to comment on each process. It is enough to add a comment where you encounter any difficulty or find an alternative solution that needs clarification. Comments are also convenient if you need to return to the code that you did a month or even several years ago and quickly parse it.

Leaving a comment on a line does not require much effort – just add // at the beginning or use the shortcut Ctrl + K + C. To uncomment a line, press Ctrl + K + U.

2. Take advantage of the script editor

If you do not want to run a part of the script for one reason or another (for example, it creates problems in the application), you can comment out it.

To do this, select text in QlikView and right-click. In the context menu, select “Comment” or “Uncomment”.

In Qlik Sense, you need to select the text, and then click on the comment icon.

To comment out the entire section of the script, place the appropriate markers at the beginning and at the end of the section:

/ * marks the beginning of a commented section.

* / marks the end of the commented section.

It looks like this:

Additionally, to make the code readable, you can align the selected lines of the script using the Tab key or the Shift + Tab combination.

3. Write code faster

Macros will help speed up work in the QlikView script editor. To get hints for them, use the combination Ctrl + Q, S, C (CTRL + Qlikview ShortCut) while in the editor. It will open a window with all the keyboard shortcuts.

4. Backus-Naur uniform

The syntax of the QlikView command line and scripts are described in a notation called Backus-Naur Form (BNF). It is one of the metalanguages ​​used to describe programming languages ​​in order to standardize them. Defining a clear language structure is very important for it to be correctly converted to machine language during script execution.

To see the BNF code syntax, in the QlikView script editor, press CTRL + Q + B + N + F.

5. Plan your escape

If you have a complex script that needs to be debugged at some point, don’t panic.

A great way to take control of this process is to create a tab with the “EXIT SCRIPT” command. Moving it among other tabs, you choose exactly when you want to complete the script.

After debugging, be sure to remove the tab.

6. Get rid of everything you no longer need

Temporary objects must exist temporarily. That is why after carrying out the necessary calculations and transformations, temporary tables or fields must be deleted.

Thus, not only do you free up memory, but also avoid synthetic keys and loops, which negatively affect the data model and disrupt the display of data in dashboards.

To prevent such problems, develop two habits.

First, check all the messages that you receive after the completion of the script. They may contain information about the existence of circular references, which must be eliminated in time.

Secondly, upon the outcome of data loading, verify using the system fields that the loaded tables do not contain temporary keys. If they are found, remove them immediately.

Having developed this routine, you will ensure there won’t be any unpleasant “surprises” in the future.

7. Limit the load

If you need to debug a table with a lot of rows or containing complex joins, you do not need to load all the records.

After launching debugging mode on the quick access panel, you can specify in the opened window the number of records you want to download. The First prefix to the LOAD statement solves this problem as well.

This can also be done in Qlik Sense, as shown below:

8. Usefulness of the third ApplyMap parameter

Mapping tables greatly simplify the life of the developer when working with scripts in QlikView and Qlik Sense. With their help, you can find a single value in another table and replace the field values ​​during script execution without loading unnecessary data.

Using the third parameter of the ApplyMap function, you can find values ​​that you may have missed. The function returns them if they were not found in the table.

For example, try ApplyMap (‘MyMappingTable’, LookUpField, ‘No match’).

Look for the value “No match” or “NA” in this field to identify gaps in the matched data.

9. Guaranteed error detection

When working with include files, using the $(Include) command is not worthwhile, because if the include file is not found, you will not receive an error message.

In this case, it is better to use $ (Must_Include), which is guaranteed to cause an error message if the include file is not found.

10. Table Viewer / Data Models Viewer is your indispensable helper.

We have already mentioned that by using system fields you can detect temporary tables and circular references. You will achieve the same result using the Table Viewer function.

Moreover, Table Viewer in QlikView and Data Model Viewer in Qlik Sense are especially helpful when you add tables or make changes to the links between them.

These tools will help find incorrect joins, synthetic keys, and randomly created island tables before they cause data inconsistencies.

11. If is not always the best solution.

Having diagrams and table expressions that contain a huge amount of data, it is better to refrain from using the if statement. If () processes data line-by-line, which as a result may slow down your applications.

In this case, Set Analysis will help achieve better performance. The ability to use it is essential for effective work in QlikView and Qlik Sense, since set analysis is one of the foundations for creating dashboards. It will facilitate your work if you need to compare different time periods or create selections that are different from the general data set.

12. Detect locked files

In the case of an odd QlikView reload, when you know for sure the script does not cause this problem, pay attention to the QVD files and logs.

Under some circumstances, one of these files can be locked. As a result, you see an error in the LOAD statement, as well as when you try to reboot the system.

In this situation, close all instances of QlikView Desktop and try launching it again.

13. A sudden reboot? No problems!

For everyone who spends a lot of time creating a good project, it’s a huge tragedy to lose it due to some error in the application. Fortunately, QlikView has a useful option that allows you to save a document before reloading.

Thanks to it, you do not need to worry about the safety of your report, when one small error leads to big troubles in the script.

14. No need for data

In QlikView and Qlik Sense it’s possible to launch a recently opened application without data.

This will come in handy when your application holds a large amount of data or you inadvertently created a monster with poor joins.

To do this, in QlikView, click on the “Home Page”, then on “Recently Opened Documents” and, having selected the desired document from the list, right-click and select “Open without data”.

In Qlik Sense, you just need to right-click on the application in the hub and select “Open without data.”

15. Saving data from temporary tables

Sometimes we rely on temporary tables to process the data again or store them separately before they are combined. Of course, at the end of the script, they are gone.

Debugging problems in such tables may seem difficult, but there is a solution. You can store them into a QVD file using the STORE command. This way you can save all data after a reboot. Then you can view QVD files with EasyQlik QViewer.

16. Scaffolding

When working with new data, try using the QUALIFY * command at the beginning of the script when loading tables from the source. This will keep the tables separated until you decide which key fields are needed.

Presenting data in the form of table blocks greatly simplifies the initial stage of work. After that, you can easily move on to charts and straight or pivot tables.

Some experts call this method “scaffolding” – the frame (table blocks) disappears as soon as a permanent structure takes shape.

17. Unified calendar

QlikView and Qlik Sense developers often deal with certain dates in the application (financial periods, time ranges, and much more). To organize the workflow and avoid confusion, use the master calendar.

Make it a rule to attach the data in the application to one calendar, avoiding multiple data selections. Otherwise, you run the risk of confusing the user and complicating your work while maintaining separation.

Speaking about the organization of work …

18. Store information in one place

We often must work with data from different systems. Naturally, each system has a fact table incorporating dates and a series of dimensions.

To steer clear of potential chaos in the QlikView and Qlik Sense associative models, gather all the data in one table. This will help simplify joins and generally works efficiently.

Important! Before combining, make sure that the aggregated fields have different names so that the data does not suddenly mix.

19. Copy the colors …

Another convenient option in QlikView is ability to copy color from any source. This also applies to copying colors from an expression where they are selected using logic.

You just need to right-click on the color field and select “Copy.” Having decided on your destination, select “Paste All” and you’re done!

20. … and even expressions!

In QlikView, among other things, you can copy expressions from one object to another. Moreover, you are not just copying text, but all formatting, including colors, text formatting, etc.

As before, use the right click to copy and paste as the need arises.

21. Gone! Or not?..

While we’re on the subject of QlikView, it’s worth mentioning the developer can use the “conditional show” feature to hide certain objects and sheets until they need to be displayed.

Very convenient, but if you make a mistake in the logical condition, you can say goodbye to them.

In this case, do not despair. To restore them, follow these steps for the sheets:

Document Properties> Sheets

To restore objects:

Sheet Properties> Objects

22. Qlik Community as a platform for collaborative problem solving

Faced with a problem in QlikView or Qlik Sense, look for a solution on the Qlik forum. Surely you are not the first to go through this. If not, you can ask a question and people will gladly help you.

In addition, do not forget about the similarity of the scripting language and functions in QlikView and Qlik Sense. Solutions for each of them are useful in most cases.