Author: BI Consultant – Richard de Gruchy
Here are 21 tips and tricks to help you with developing and designing applications using QlikView and Qlik Sense. Most relate to both products but some are QlikView specific. This is clearly shown in each section. Some are technical, some help when building any objects.
- Make a Comment
- Commenting out Statements
- Make your Exit
- Quick Drop
- Stick to the Script
- Limit your Load
- Apply Map Default
- A Tip to Include
- The Table Viewer/Data Model Viewer is your friend!
- A Bit Iffy
- Crash Test
- Better ‘Save’ Than Sorry
- Keep the Facts out of it
- More Permanent Temporary Tables
- One Calendar to Rule them all
- Keep all your Facts in one Place
- Colour Copier
- Interesting Expression
- Where did that Object go?
- If in Doubt, Google it
1. Make a Comment
Adding comments to your script can help others understand what is happening. Don’t waste your time commenting on every process, just add comments where there is complexity or where you have deviated from what others may expect you to have done.
Comments help you when returning to something you did months or years ago. (Or days in my case!) To comment a line just add // at the beginning.
2. Commenting out Statements
You can comment out parts of your script if you wish not to run it or it is causing some problems for you.
In QlikView, mark the text and then right click to comment or uncomment.
In Qlik Sense, mark the text then use the comment icon.
You can also comment out a whole section of the script by placing markers at each end.
/* to mark the start of the commented section.
*/ to mark the end.
3. Make your Exit
If you need to debug a complex script, create a tab with the command ‘EXIT SCRIPT;’ in it.
You can move this tab around to terminate the script after any of the other tabs.
4. Quick Drop
If you have temporary tables, drop them as soon as you have finished with them.
This saves memory and also makes it easier to debug as each tab removes the temporary tables so you have less chance of duplicated data and synthetic joins.
5. Stick to the Script
If you have calculations and data manipulation, try to do this in the script.
There is more power and flexibility in the script.
Having the hard lifting done in the script makes the dimensions and expressions easier for you and others when building visualizations.
It can also boost performance as CPU time and RAM are not taken up on logic and number crunching when users make selections in the apps.
6. Limit your Load
Where you have large tables with long load times or complex joins, you can always limit the number of records loaded with the debug mode.
This is available in QlikView and Sense as per below.
7. Apply Map Default
When using mapping tables, use the third parameter (the default) of the ApplyMap command to catch values you may have missed.
Eg. ApplyMap(‘MyMappingTable’, LookUpField, ‘No match’)
Search for the value ‘No match’ in that field to find holes in the mapping data.
8. A Tip to Include
If using include files, the $(Include) command will fail silently (no error message will appear) if the include file cannot be found.
Use $(Must_Include) instead as this will cause an error message if the include file is not present.
9. The Table Viewer / Data Model Viewer is your Friend!
Use the Table Viewer (QV) or the Data Model Viewer (QS) each time you add tables or make changes to the links between tables.
This will help you spot incorrect joins, synthetic joins or accidental island tables before these things cause data inconsistencies.
10. A Bit Iffy
Avoid using if statements in charts and table expressions especially if you have a large amount of data.
This logic is quite a heavy load and can slow your apps down.
Set analysis is a better option …
… but that’s a topic for another day!
11. Crash Test
If the QlikView reload is mysteriously crashing and your script is seeming not to be the culprit, it may be a locked QVD file or log file.
Under some circumstances, one of these files can become locked and will refuse to load causing an error on that LOAD statement or when starting the reload.
Try shutting down all instances of QlikView desktop and then try again.
12. Better ‘Save’ than Sorry
In QlikView, there is a handy option to request the system to save your document before a reload takes place.
This is really useful in preventing your beautifully crafted script from being lost because of a teeny weeny bug your accidentally introduced that caused a fatal script failure.
13. Keep the Facts out of it
In QlikView and Qlik Sense, you can open a recently opened app without the data.
This is useful if you have a huge amount of data in the app or if you have created a monster accidentally with a bad join.
In QlikView, click on the Start Page, then Recently Opened Documents, select the one you want and then right click and select ‘Open <app name> Without Data’
In Qlik Sense, right click on app from the hub and select ‘Open without data’
14. More permanent temporary tables
Sometimes we use temporary tables when data needs to be processed again or we have tables in one state before they are joined. These tables are cleared out before the end of the script.
It can be difficult to debug issues in these tables as they are, well, temporary. One trick to help out is to use the STORE command to write them out to a QVD. The QVD file can be examined after the reload to see what data was in the table at the time.
When working with new data, loading tables from the original data source using the QUALIFY * command at the beginning of your script will ensure all tables are separated until you can work out which are the key fields you need.
Using table boxes to present data while investigating can be a quick way to get started. You will likely move on to charts, straight or pivot tables later.
I call this scaffolding as it all disappears when the permanent structure goes in.
16. One calendar to rule them all
Most applications use some kind of date selections (financial periods, date ranges etc.). The best way to work with this is by using a master calendar.
Generally, all data in the app should be linked to the master calendar. Try to resist using two or more date selections as this will become confusing for the user and lots of work for you to maintain the separation.
Leading on nicely to …
17. Keep all Your Facts in One Place
We are often working with data from more than one system. Each system usually has its fact table with dates and a mix of dimensions.
One way to get around the potential mess that can ensue in an associative model (as used by QlikView and Qlik Sense) is to concatenate all the facts into one table. This simplifies the joins and works very well.
The only caveat to this is to make sure your aggregated fields have different names so the two or more types of data do not mix unexpectedly.
18. Colour Copier
QlikView allows you to copy and paste colours wherever they are used. This even includes colours that are defined using logic in an expression.
Just right click on the colour box and then select ‘Copy’, find the destination for that same colour and then select ‘Paste All’.
19. Interesting Expression
QlikView also allows you to copy expressions from one object to another. This is better than just copying the text of the expression as it copies any expression formatting eg. Colours, text formatting etc. also.
Just use right click to ‘Copy’ or ‘Paste’ as required.
20. Where did that Object Go?
In QlikView, you can use the ‘conditional show’ feature to have sheets and objects be hidden until you need them to show.
This works really well, unless you make a mistake in the logic condition and then they can disappear without a trace.
They can be recovered using the following for sheets:
Document Properties > Sheets
Or the following for objects:
Sheet Properties > Objects.
21. If in Doubt, Google it.
There is plenty of help on the Qlik forums for both QlikView and Qlik Sense.
Remember, the QlikView and Qlik Sense scripting language and functions are almost identical so help in those areas will apply to both systems in most cases.
Want us to cover a particular topic? Leave a comment or email us at firstname.lastname@example.org.