21 Tips & Tricks for Qlik | ScopeTalk

21 Tips and Tricks for Qlik

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.

  1. Make a Comment
  2. Commenting out Statements
  3. Make your Exit
  4. Quick Drop
  5. Stick to the Script
  6. Limit your Load
  7. Apply Map Default
  8. A Tip to Include
  9. The Table Viewer/Data Model Viewer is your friend!
  10. A Bit Iffy
  11. Crash Test
  12. Better ‘Save’ Than Sorry
  13. Keep the Facts out of it
  14. More Permanent Temporary Tables
  15. Scaffolding
  16. One Calendar to Rule them all
  17. Keep all your Facts in one Place
  18. Colour Copier
  19. Interesting Expression
  20. Where did that Object go?
  21. If in Doubt, Google it

1. Make a Comment

QlikView AND Qliksense

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

QlikView AND Qliksense

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.

making comments - images

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.

Example:

making comments Part 2 - images

3. Make your Exit

QlikView AND Qliksense

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.

make your exit

4. Quick Drop

QlikView AND Qliksense

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.

quick drop

5. Stick to the Script

QlikView AND Qliksense

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

QlikView AND Qliksense

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.

Limit your load

7. Apply Map Default

QlikView AND Qliksense

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

QlikView AND Qliksense

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!

QlikView AND Qliksense

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

QlikView AND Qliksense

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

Qlikview

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

Qlikview

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.

better save than sorry

13. Keep the Facts out of it

QlikView AND Qliksense

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’

Keep the facts out of it

14. More permanent temporary tables

QlikView AND Qliksense

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.

15. Scaffolding

QlikView AND Qliksense

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

QlikView AND Qliksense

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

QlikView AND Qliksense

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

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

Colour Copier

19. Interesting Expression

Qlikview

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?

Qlikview

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

where did that object go

Or the following for objects:

Sheet Properties > Objects.

where did that object go 2

 

21. If in Doubt, Google it.

QlikView AND Qliksense

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.

if in doubt google it

 


Follow us on LinkedIn and Twitter to receive updates on upcoming ScopeTalk blogs.

Want us to cover a particular topic? Leave a comment or email us at marketing@scopesystems.com.au.

Related Posts


Converged Vs Hyperconverged Infrastructure? | ScopeTalk Author: Client Executive - Jamie Halliday Why H...
Why Choose Qlik? | ScopeTalk Author: BI Consultant – Linda Bell   Un...
The 2016-17 Mining Year in Review | ScopeTalk   Bullish or Bearish…The Year in Review Author: ...

Take the next step.

Get the help you need now. One of our experts will call you to discuss your needs and help start you on the path to greater control.

© Scope Systems Pty Ltd
Business Management Software Solutions
Scroll Up