QlikView and Qlik Sense Tips and Tricks
undefined
QlikView and Qlik Sense
Tips and Tricks
By 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 slide
Some are technical, some help when building
any objects
1. Make a Comment
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
Adding comments to your script can help others understand what is
happening
Don’t waste your time commenting 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 (1 of 2)
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
2. Commenting out Statements (2 of 2)
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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:
3. Make your Exit
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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!
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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 a
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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? (1 of 3)
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
20. Where did that Object Go? (2 of 3)
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
They can be recovered using the following for sheets:
Document Properties > Sheets
20. Where did that Object Go? (3 of 3)
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
Or the following for objects:
Sheet Properties > Objects
21. If in Doubt, Google it
by Richard de Gruchy
QlikView and Qlik Sense Tips and Tricks
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
Just Google ‘Qlik ‘ or ‘QlikView ‘ and then
your topic
Thanks for listening and happy Qliking!
Learn valuable tips and tricks for developing and designing applications using QlikView and Qlik Sense. Discover insights on adding comments, commenting out statements, making efficient exits, and quick data management techniques. Enhance your skills in utilizing these powerful data visualization tools effectively.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
QlikView and Qlik Sense Tips and Tricks By 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 slide Some are technical, some help when building any objects
1. Make a Comment Adding comments to your script can help others understand what is happening Don t waste your time commenting 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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
2. Commenting out Statements (1 of 2) 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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
2. Commenting out Statements (2 of 2) 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: QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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! QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
15. Scaffolding 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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 a 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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
20. Where did that Object Go? (1 of 3) 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 QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
20. Where did that Object Go? (2 of 3) They can be recovered using the following for sheets: Document Properties > Sheets QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
20. Where did that Object Go? (3 of 3) Or the following for objects: Sheet Properties > Objects QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
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 Just Google Qlik or QlikView and then your topic QlikView and Qlik Sense Tips and Tricks by Richard de Gruchy
