19 Tips All QlikView Beginners Must Know

You download your bright, new, shiny QlikView Desktop. You are ready to start on your special BI journey to undiscovered data islands. The excitement is palpable.

Now what?

Often as a new developer, you don’t know where to start. There is so much to learn. Trying to put the puzzle together in a way that makes sense and is as efficient as possible is not always easy. There is no substitute for a QlikView training course, whether it is on-line or on-site. But to get you started in the right direction, I put together some tips. These are presented in a natural order as you might encounter them in your development cycle.

1 Diagram Your Data Model

I find it tremendously helpful to draw a diagram of my data model before loading ANY data into QlikView. The diagram does not need to be super precise or take hours. Just concentrate on the tables and the fields that will associate them to together. It may seem silly but this does a few things for you. First, it helps you visualize the relationships between the tables. Second, it alerts you to potential problems with synthetic keys and circular references. Thirdly, it helps to clarify which of the tables are your central transactional tables and which ones are the dimensional supporting tables. Lastly, the process helps to identify opportunities to simplify or optimize the data model. You will find that if you take the 10 minutes to diagram your data ahead of time, it will save hours of coding experimentation and mistakes. Here is a diagram I created for an application. You can judge whether this is a great example you should emulate, or the gibberish of a crazy-man (dare I say the latter?)

2 The Design Toolbar

You should probably add the Design Toolbar to your QlikView desktop. This gives you several buttons that you can use to initiate commonly used features like New Sheet, Format Painter, Design Grid and Table Viewer. In the main menu go to View –> Toolbars –> Design

3 Save Before Reload

I am not telling you to manually hit save before you hit reload. Instead I am telling you to set up your preferences so that a save is automatically initiated every time you press reload. This way, you don’t even need to think about it! Because when your script bombs, you want to get back to where you left off. To set this preference, in the dashboard menus, select Settings –> User Preferences. Select the Save tab and then check the box for Save Before Reload.

4 Set up Document Logging

You will become familiar with the Script Execution Window. In particular, you will get familiar with checking the Close button. And at some point you simply opt to select the little check box to automatically close the execution window. So when you need to examine entries in this log, it is helpful to have a copy of that log saved for you. If you again navigate to Settings –> User Preferences and then go to the Design tab, you will find Always Use Logfiles for New Documents. Checking that box will tell QlikView to create a text log in the directory of your QlikView file (qvw).

5 Iterate

Do one thing at a time.

  1. Code a single improvement
  2. Reload
  3. Validate

Many times, I have gotten into trouble by not following this advice. For example, I might load a table, then left join another table to, with a mapping load to get this translation and then multiply it with an existing field and then rename all my fields and transform some date fields…etc. You get the picture. It sounds like I accomplished a lot. But then upon reloading, I get the first of many errors. At this point, I haven’t the slightest idea which functions are causing the issues and then spend 2 hours debugging my work, backtracking along the way to find and correct the problems. If I had just worked in an iterative method, performing one trick at a time, I would have saved about half the time. You might legitimately say, “But Aaron, my reload takes 20 minutes to execute. If I am reloading more often, won’t that cost me way more time?” That brings me to the next few tips.

This concept will help you develop set analysis too.

6 Limited Load

When you are ready to test your script, sometimes I will enter debug mode and then run a limited load. This initiates your script capping off load statements once you reach that threshold of records. This greatly reduces the time spent waiting for your script to execute. The downside of this option is that you might not end up with any associated records between the tables, depending on how many rows you asked for relative to the size of the data. I usually enter 10,000 or 100,000 to get a representative data set. Even 100,000 will still load very quickly. For that reason, the limited load option is good for testing your script logic, but not great for doing data validation.

7 Use QVD Extracts

This is probably one of the best tips for QlikView newbies. Much of the reload time in QlikView comes from connecting to external data sources, like SQL databases. It is not that SQL is slow. It’s just that establishing a connection to another server in the network and then executing a query that returns millions of records will always take time regardless of the database. Instead, I use QVDs. QVDs are proprietary QlikView data files that reside on a file server. They hold one table’s worth of data. They will load into QlikView way faster than the same amount of data coming from an external database. So what I do is load all the data from the database tables one time and store that table in its raw form into QVDs. Then I simply comment out the SQL queries and replace them with the QVD locations, thereafter doing all my development from that QVD. This will save you all sorts of development time. When you have finished all your development and are ready to promote the app, you can switch the locations back to the original database location. In the first screenshot, you can see how I extracted and stored into QVD with my first reload. Then I adjust the script and develop with my QVDs from then on. My subsequent reloads will be much faster.     Here is an article that explains various QVD strategies including data extraction for development: LivingQlik: Learn How to Create a Layered QlikView Architecture

8 Use Path Variables

The idea of path variables is to make it easier to migrate your applications to different folders, or different servers. Whenever we load files located in file servers like qvd’s or MS Excel files, for example, these paths either need to be completely written out (fully qualified path) or relative to the location of the qvw. This is fine, but it also means that as we migrate a qvw from a development server to a production server, I must either change all the paths or I have to ensure that the relative folder structure is exactly the same between the two environments. We can avoid this issue altogether by simply creating all the paths I need for all environments and then selectively assigning them to a variable.

9 Use Standardized Naming Conventions

It is a good idea to use standard naming conventions for keys, dimension fields, expression fields and variables. This practice will make it easier to create your dashboards. Your dimensions will all be labeled for your users. Keys will be prepended so they appear at the top of lists. And metric fields will easily auto-populate in your expression dialogs. All of this adds up to time savings for you and makes you look like a professional. QlikView autopopulates field names Here is a full method for naming your fields appropriately: LivingQlik: QlikView Field Naming Best Practices

10 Concatenate Your Fact Tables

All

QlikView developers quickly find themselves in a mess of tables with circular references and synthetic keys galore. Learn the difference between your fact tables and your dimensional tables. Consider concatenating your fact tables together with common keys. This will greatly simplify your data model, reducing or eliminating synthetic keys and circular references.   No doubt this is a complex topic. Here are some resources to help you. LivingQlik: Link Table vs. Concatenation – A Case of Mixed Granularity Qlik Community: Fact Table with Mixed Granularity Qlik Central: Should I Link or Concatenate

11 Mapping Load is Your Friend

As I have said over and over, mapping load is my favorite QlikView scripting function. The main reason this function stands out is because it can be used for a variety of use-cases where left join is less optimum or won’t work. The idea is simple. We can substitute in a value from a mapped table to a recipient table. But we also have the ability to specify an alternate substitution when no match is found. You can read all about mapping load here: LivingQlik: The Complete Guide to QlikView Mapping Load Mapping Load - Syntax Example  

12 Limit Your Data

Once you start loading tables, you will be quickly overwhelmed in the front end. “There are so many fields”. I find it helpful solely from a user/development perspective to reduce the number of fields in my QlikView documents. This also saves vital active memory (RAM) on our QlikView server or our local desktop if opening locally. If you do not have a need for the field in the UI or need it for a key, get rid of it! I do the same thing for extra rows. Why bring in 20 years of data when I am only concerned with the last 6 months? Use where clauses to trim down the data tables. This two-sided approach (rows and columns) will pay off dividends in usability, ease of maintenance and performance. The techniques for this are covered completely here: LivingQlik: Reducing Rows & Columns in your Qlik Data Model

13 Learn to Create a Calendar for Your Apps

Most QlikView applications utilize time as a dimension. And usually these users will need to divide facts by various time dimensions including Year, Month, Week, etc. One skill you need to pick up is adapting a calendar to your Qlik apps. This usually involves identifying and formatting your fact table date field and loading in a generic script to create your Calendar table. Once you do this a few times, it becomes pretty simple. LivingQlik: How To Add Useful Date Groups to your Qlik Calendar

14 Tableboxes Are a Development Tool

I rarely use the Tablebox object in QlikView production applications. But I do utilize them when I am developing. Tableboxes are a great way to test if your data tables are connecting together appropriately and to see if there are any transformations required to get your data looking good for your users. They can be added quickly to your sheet as they do not require any expressions. Just add the fields you want to visualize and the Tablebox object will show you all the unique combinations of the data.

These Dates are supposed to line up. The tablebox alerts us to the problem.

15 Set up Your Listboxes and Then Copy Sheet

You will likely have listboxes, multiboxes, a current selections box and maybe some branding objects like a company logo on each page. And mostly, you want these items to remain consistent across all your QlikView sheets. Generally, I create this “selection layout” and then right click à copy sheet to create ready-made sheets for all subsequent views. This will save you time AND will keep your layout consistent.

16 Dimensions and Expressions First – Cosmetics Later

When developing front-end charts and visualizations, it can be tempting to go through each of the property tabs and check all the boxes you think you will need. This is fine for experienced developers but you could possibly select options that effect the availability of other options, getting you into a hole that you can’t easily back out of. And because you took all these options at once, it is sometimes difficult to figure which option messed up your visualization. I would instead recommend doing 4 things when creating any visualization before getting into any cosmetics:

  1. Select the visualization type
  2. Add the required dimension(s)
  3. Add the required expression(s)
  4. Select OK and make sure the chart works.

By following these steps, you know that you the fundamental workings of the chart are solid. Then you can go back into the properties and tweak the look of the visualization to your liking.

First, get the chart working.

 

Then you can make it look nice.

17 Put Your Common Expressions into Variables

I often start creating my expression syntax in each object. The expression dialog has the tools that I need to create and test this syntax effectively. But when I have my basic expressions created I will house each of them in a variable so that I can reference a uniform expression definition every place I use it. This will save you time when you have to change that definition because you will only have to update the definition on the variable rather than having to visit each object. You can create these variables in the variable overview Settings –> Variable Overview. You might instead opt to create these variables in the QlikView script with a set function. Both methods will help you maintain common definitions for your metrics.

18 Memorize the Shortcuts

There are a plethora of routine things you will do like initiate the Edit Script module (ctrl-e), the Table Viewer (ctrl-t) or perform a Reload (ctrl-r). All the common tasks in QlikView have a shortcut. Also, keep in mind that all your traditional Windows shortcuts like Copy (ctrl-c) and Paste (ctrl-v) work as well. Get to know the common shortcuts. They will save you clicks! Living Qlik: QlikView Designer Quick Tips and Shortcuts LivingQlik: Top 5 Little Known QlikView Shortcuts

19 Lean on Resources

You cannot do this alone! Thankfully there are many resources online that can assist you in your development learning. Google is your friend.   This article from Quick Intelligence is a great resource for all Qlik Developers looking for a place to go for their specific need. This article links out to resources for many common needs. Quick Intelligence: The Qlik Developers Toolbox   The Qlik Community is awesome. Every conceivable question has been asked and answered here. I leverage to this day. You can view anonymously, but you should register (free) to participate in the community. The Qlik Community   The blogosphere is rich with depth for the Qlik universe. There are over 2 dozen blogs being written by developers all over the world just full of in-depth information on all things QlikView. AskQV helps aggregate these blogs so that you can find the author, blog, article you need. AskQV (Blog Aggregator)

Final Thoughts

Learning a new skill in the BI world is a daunting task for anybody. But it is an exciting task. Hopefully, these tips will help you get on your way. I welcome your suggestions.

Happy Qliking!

9 comments… add one
  • Reply Steve Dark March 28, 2017, 1:34 pm

    Hi Aaron,

    Great article! Thanks for sharing.

    Many thanks also for the reference to my blog and to the AskQV site. We have some exciting enhancements planned for AskQV, to be announced on the blog at some point soon…

    Steve

  • Reply Ali Shan Khaliq March 28, 2017, 2:28 pm

    Hello Sir
    Great Information thanks for sharing.. i am working with QlikView since a year but i don’t give us time continues to my project.. this is major reason for i did’nt get any job. need your little bit bit advice i am beginer in qlik let me how to be a professional Qlikview developer..

  • Reply Aaron Couron March 29, 2017, 1:22 am

    @Steve ~ I link to your site because it is invaluable to the Qlik Eco-System. I find that the Qlik community is a nice broad shallow bay for me to swim around in. AskQV represents the depths of the Qlik pool.

  • Reply Danilo Dalosse March 30, 2017, 5:02 am

    Realy realy good article. Congratulations Aaron!
    I’ll share…

  • Reply Bill Markham March 31, 2017, 9:23 am

    I was gobsmacked when I was first told that if one selects a chart object and presses then red lines appear around the sub objects allowing to drag the lines to resize / move them.

    This is my suggestion an extra tip.

  • Reply Bill Markham March 31, 2017, 9:48 am

    Ooops!! Bad typo – one has to press

  • Reply Bill Markham March 31, 2017, 9:50 am

    Strange – I typed the keys to press in triangular braces, hit submit and the braces and text between disappeared – it must have thought I was trying to do some html.

    Control and Shift

  • Reply Aaron Couron April 3, 2017, 5:56 pm

    Yes Bill, the red borders are pretty cool, especially the first time you discover them, lol.

  • Reply Richard de Gruchy September 28, 2017, 7:42 am

    Hi Aaron
    This is a great article. Thanks for posting it. I have been putting together a PowerPoint with a selection of tips and tricks for QlikView and Sense, some of which you have here, some not. There are also a couple you have here that I would like to add, if that is OK.

    I will email it through to you when finished.
    Richard

Leave a Comment

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons