QlikView and Qlik Sense Tips and Tricks
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
