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.
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).
Do one thing at a time.
- Code a single improvement
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.
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. 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
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.
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:
- Select the visualization type
- Add the required dimension(s)
- Add the required expression(s)
- 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.
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)
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.