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!

3 comments

The Qlik Sense Wishlist Results Analysis

A few weeks ago, I published a post 16 Items on My Qlik Sense Wishlist. It was just a “List to Santa Qlik” on the features that I would like to see in Qlik Sense. This post seemed to resonate with LivingQlik readers. Many people added their own wishlist items or voted on the features from the article they would like to see. This post has definitely garnered the most audience participation LivingQlik has ever seen which is just awesome. 30 post Comments – I know this really isn’t that much, but it is a record for me. 80 Twitter engagements – This is what Twitter Analytics says. Not really sure what it means, lol. 100 LinkedIn shares – Provided by BuzzSumo. Anyway, I thought it would be fun to compile all the comments I received where readers submitted their own wishlist items and analyze the results in a Qlik Sense application, of course! This post is really just a thank you letter to all the great people out there that shared their thoughts and opinions with me.

Resources

I utilized the following extensions for this app: Qlik Branch: Word Cloud Qlik Branch: Color Styler NarrativeScience: Narratives for Qlik Sense

 

Qlik Sense Wishlist Analysis

The data source is just a 2-column spreadsheet. Here is the Qlik Sense application if you want it: Wishlist-Results.qvf

  • The most votes by far was for Dynamic Labels in Qlik Sense. As described in the post, the ability to calculate the title of a metric is nothing short of vital to the success of an application.
  • The idea of adding some sort of Dollar-Sign Expansion Preview was popular as well. It’s one of those things that I took for granted in QlikView until I realized there was not an easy way to test this in Qlik Sense.
  • Surprisingly, the Only One Selected Value feature was not too popular among the readers, although that is a function I rely on in QlikView.

Here is the complete list: Qlik Sense Wishlist Results - Bar Chart   Here is a word cloud of the features that Qlik Sense developers are wishing for.

  • Do word clouds have tremendous analytical value? I don’t think so.
  • Are word clouds really fun to look at? Completely.

Qlik Sense Wishlist Results - Word Cloud   Below is a tree map of the sources for reader engagement. People felt the most natural leaving comments on the post itself. LinkedIn was another strong forum for participation. Twitter is fun, but did not create too many comments. Facebook is a waste of time normally but I still utilize it for some odd reason. Qlik Sense Wishlist - Tree Map by Source   NarrativeScience has a great premium extension that applies natural language analysis to charts in your Qlik Sense applications. It is very impressive technology. I utilized the extension here to do an automatic analysis on the wish counts. It speaks for itself, literally. Qlik Sense Wishlist - Narratives for Qlik Analysis  

Final Thoughts

I have submitted this list to somebody at Qlik. Not sure where it will go, but thought you should know. Although running a blog is admittedly time-intensive from an author standpoint, it wouldn’t amount to much if nobody cared to read it. So I sincerely thank you for taking the time to read the posts, and maybe even participate with me in this journey like so many did for the Wishlist post. We all work together to make a better Qlik ecosystem. So thank you again. Happy Qliking!

3 comments

8 Qualities of the Absolute Best Qlik Developers

Featured Image - 7 Qualities of Great Qlik Developers

Teaching new developers is part of my job description. It is definitely something I enjoy doing. I am frequently asked what qualities new developers should possess to excel with QlikView or Qlik Sense. And usually they are talking about technical experience. “Should I know SQL?” Although there are always technical requirements, I have always been way more concerned with having the right attitude. Given the correct mentality, all the technical stuff will follow. I often wonder what makes a good QlikView and Qlik Sense developer. I reflected on all the students I helped train and my own skills (or lack thereof) and came up with a list of qualities and skills that I believe are essential for a Qlik developer.

Warning! This list is highly subjective and only reflects my opinion.

1. Natural Curiosity

Natural CuriosityEvery great developer I know exhibits a “thirst for knowledge” about the Qlik platform. What I mean by this is that when a problem is presented that a good developer does not know the answer to, they go find out on their own. And they do it as much for their own knowledge as they do to serve the client. A good Qlik developer is continuously learning (even when they are not getting paid for it). They are always watching Qlik videos, researching the community, reading blogs, and getting education.  

2. Personal Qliking

Personal ProjectsIn my experience, all good developers have this tendency of creating various Qlik apps for their own use. These might be business related apps or apps for a personal purpose, but the sole audience is the developer herself. This happens because the good developer understands how useful Qlik really is and just naturally finds uses for it in their daily life. I have said before that I have apps Qlik apps that I use for my personal finances, invoicing and for monitoring my exercise routines as examples.  

3. Quick Responder

quickresponderAll good developers I know are quick responders. When a customer comes with a question, the model developer is on it within 24 hours. By nature, Qlik projects always have short development cycles relative to other BI tools. This tendency creates a culture of speedy communication where the project happens more like a live conversation than exchanges delivered by certified mail. I find that the developer that sits on an email for a week simply doesn’t understand the needed flow.  

4. Excellent Communicator

CommunicationThis is a broad topic. Good Qlik developers can communicate clearly and pleasantly to everyone. Good Qlik developers also know their audience. Even more importantly, they know to cater their vocabulary and tone to the person they are talking to, saving the advanced technical terms for other developers and using a normal business vocabulary for the analysts and other users.  

5. The “Many Hats” Mentality

Many HatsThe best Qlik developers can wear “Multiple Hats”. This is because we are asked to architect the data model, create ETL script, design visualizations, train users or other developers, help sell the software, set up servers, understand other connected software, etc. The list goes on and on. A developer that is willing to take a stab at many of these will be a good developer. Developers that feel their role should be limited to creating pie charts is doomed to failure.  

6. Bridge Building

Bridge BuildingIn my experience, there are lots of disparate people involved in any BI implementation. There are salespeople, database administrators, data experts, network administrators, business users, analysts, executives, and on and on. If all these people are not connected in the process, the project fails. I find that the QlikView or Qlik Sense developer is in the middle of all this and often times needs to be the person that connects all these disparate people together. A good Qlik developer understands this and will make that extra call to the business user to make sure they can access the dashboard, or will remember to send that follow up email to remind the DBA that she needs permission to read a particular database. The model Qlik developer is always making sure these “bridges” are built.  

7. Communicates the Qlik Vision

VisionThis is an interesting one. When I say “Communicates the Qlik Vision”, I am not really talking about regurgitating the company marketing taglines. Rather, after receiving the dashboard requirements for a project that might reveal a misunderstanding of the Qlik value proposition, a good developer will communicate how we might change those requirements to take advantage of the way Qlik does things resulting in a dashboard that does more than the business requirement originally dictated. For example, with Qlik Sense, I still get the question “Can I export this chart to Excel?” The technically correct answer is “Yes”. But a question like that is really begging me to show the user how to pivot the columns of the chart within the interface or how to drag in another measure column. The good developer works with users to teach them how to use Qlik the way it should be used rather than just being a “yes” man to every customer whim.

8. Gives Back to the Greater Qlik Ecosystem

Helping your own customers is not good enough for the best Qlik developers. The greatest Qlik developers are involved in extra-curricular Qlik activities that spread knowledge across the Qlik universe. This could be participating in the Qlik Community, helping out with the Qlik Dev Group, becoming a Qlik Luminary, taking on speaking engagements, publishing a book or writing a blog (my preference). Even if your aren’t yet the greatest Qlik developer, taking on a few of these extra activities only serves to round-out and grow your knowledge and also grow your network.  

Final Thoughts

Note that nowhere did I say that a good developer needs to be an expert in QlikView. This is because I feel that if you exhibit all the qualities above, the expertise will quickly come to you. Personally, I value attitude over knowledge all day long. Maybe you are a Qlik developer or are thinking of becoming one. Do you display these traits? Do you agree with this list? I welcome your comments below. Happy Qliking!

10 comments

Information Dashboard Design – Laying the Foundation of Data Visualization [Review]

Featured Image - Information Dashboard Design - Review

LivingQlik is an Amazon Affiliate.

In this post, I will review Stephen Few’s Information Dashboard Design. Regarded by many as the “Bible of Dashboard Design”, if you design data analysis interfaces (ie. dashboards) for business users, you had better own this book.

Who is Stephen Few Anyway?

The name Stephen Few is well known in BI circles. He has been consulting in this space for over 30 years at Perceptual Edge. He has written several books on the subject and even introduced us to a new visual data object, the bullet graph.  

So What is Information Dashboard Design All About?

The mission of Information Dashboard Design is to teach the dashboard designer how to analyze and create data visualizations that will be highly accurate, focused and helpful for business users. Few leads us down this path in a way that is, I dare say, entertaining. The language is plain and straightforward and there are plenty of pictures, which is part of the fun. Let me explain. The first three chapters walk us through the “What not to do” mistakes that designers commonly make. Some of the flaws are quite obvious when you see them. And a few are more subtle. The best way to read this section is to first examine the dashboard and try to guess what the mistake is and then read the explanation. I bet you can’t figure out all of them. I know that I didn’t. For example, an obvious problem would be the slanted or “3D” pie chart. We have all seen these.

How do the slices at the back compare to the slices at the front? Hard to tell?

The problem here is that because we are forcing a perspective on the chart to make it look three dimensional, we are shrinking the slices in the top (rear) of the chart and exaggerating the slices that are front-facing. Anything that distorts the perception of the visual is obviously a bad thing. Another not as obvious issue would be encoding value of good or bad solely with the colors green and red. With 8% of men and .05% of women having some form of color blindness (red/green being the most common), do you really want to design a visual that leaves out 1 in 12 people? ColourblindAwareness.org

If you see no number or 21, you might have some form of colorblindness.

There is no need to completely stop using green and red, but one suggestion would be to utilize an icon along with the color to signify direction or judgement.

We can still use red to denote bad” but the icon displays this to colorblind users without relying completely on color.

After tearing several dashboards to shreds, the middle act of the book sets to establish the guiding concepts for good dashboard design. Few teaches how humans perceive data through visual objects, emphasizing how we quickly glean the data we need but also how we may rapidly forget that data as we click over to another page of the analysis. Simplicity is a consistent theme with Few as well. Any extra ink on the page only serves to distract the user from the important data. The remaining chapters deal with specific techniques and objects to build an effective dashboard. Here Few constructs dashboards that exemplify good design and gives detailed explanations as to why they are used and how the visualizations work together. He also introduces the Bullet Graph. This is a unique object because it works as a gauge but cleanly and concisely gives multiple context to the value while also allowing for a target value.

Final Thoughts

Information Dashboard Design is a quick read, but just completely packed with immediately actionable knowledge. In fact, I guarantee it will make you want to go back and redesign your own dashboards. *** Edit *** I have been made aware that there is a second edition of this book that includes more of the positive examples of good dashboard design. Here is a link: Information Dashboard Design: Displaying Data for At-a-Glance Monitoring   Do you have a reading suggestion? Please let me know in the comments below.

Happy Qliking!  

12 comments
Page 1 of 17
1 2 3 17

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

other sites

ASKQV One Spot – Many QV Blogs
Visual Data Group My QlikView partner
Official Qlik Community The great Qlik Community
QViewer Handy QVD Viewer

Show Buttons
Hide Buttons