The QlikView internal logic enables a data model with several associated tables. It not only allows – it encourages you to use several tables when building a data model. This is very different from many other BI or query tools where, when several tables are used, they are all are joined together into one table. The most obvious example of this difference is a simple SELECT statement. With it, you can use several tables as input and join them, but the output is always one single, denormalized table. With QlikView, in contrast, you can have a multi-table relational data model that is evaluated in real-time. The associations are evaluated as joins at the moment when the user makes a selection in the application. At the same time, all objects, some with complex calculations based on these joins, are recalculated. When creating the QlikView data model, you have a choice of loading the tables as several entities or joining some of them together. Joining in the script means that the result of the join is stored in the QlikView data model as one single table. So what should you do? Is it better to keep the data model normalized (many tables) or is it better to de-normalize (fewer tables)? My view is that it usually is better to keep the data model as normalized as possible. A normalized model has many advantages: It is memory efficient. It is, by definition, the data model that uses least memory. It is CPU efficient. In most cases, QlikView calculations in a normalized model are as efficient - or only marginally slower - as in a denormalized model. In some cases the normalized model is faster. It is easier to understand and manage. It should be possible for other developers to read your script: A simple script with as few transformations as possible, is a script that is easy for other developers to understand and maintain. It minimizes the risk for incorrect calculations. Joins potentially change the number of records in the tables, which means that a normal Sum() or Count() function cannot always be used – they would sometimes return an incorrect result. You may counter that there is always a way to write a correct formula, but my point is that it should also be easy. Expressions in server objects will be written by users that do not have special knowledge about the data model in the app. But it is not a clear-cut case. Often there is a trade-off between memory efficiency and CPU efficiency. In other words, there are cases where you can decrease response time by letting the data model use more memory; where performance will be better if you make the join in the script. One such case is if you have two very large fact tables, like Order Headers and Order Details. An other is if you have chart expressions containing fields from different tables. Then QlikView has to perform the join in memory generating a virtual table over which the summation will be made. This can be both memory and CPU demanding, so you might get a better performance if you have made the join already in the script. But the difference is sometimes only marginal. You need to test, to be sure. Bottom line is that you’ll have to weigh pros and cons. Don’t join unless you have to. If performance is important and you experience a noticeable improvement when you join, then you probably should join. But ask yourself what the implications are. Is the script still manageable? Can a user understand how the formula should be written? The best join is often the one that never is made. Often – but not always. HIC See more about this topic in the Technical Brief about Joins and Lookups ...View More

Recently, I was asked to create an application in which a user would be able to search for videos for the demos on demo.qlik.com. After reviewing the task at hand, I decided that I would need to incorporate a QlikView extension in order to have the videos play inside the QlikView application. The extension that I chose was the webpageviewer.qar. My dataset included the URLs for the videos. The videos were previously uploaded to YouTube. By uploading the videos to YouTube, it allowed me to embed the videos in the webpage viewer so that only the video would show up in the webpage viewer instead of the entire webpage. Another aspect of the application was to give the user the ability to select tags in order to filter the data and find the desired video to play. As a team we decided that, because there were so many tags to display, it would be better to show them as a popup over the other objects on the page. The user could invoke this popup by selecting the “View all video tags” button. The user also had the ability to remove the popup from the screen by selecting the close button. Easy enough, right? All it entailed was creating a couple of variables, a text box and a list box for tags and layer them all accordingly. This was standard QlikView show/hide. Not so fast my friend! Did you know that the webpage viewer takes layer precedence over all other objects on the screen? That’s right; you cannot layer over top of the webpageviewer because of the flash used for the videos. The webpageviewer always comes to the top. OK, so now what? I decided that I needed to somehow put a show/hide condition on the webpageviewer and hide it when the popup was selected. Only one problem, the original webpageviewer did not have an area to write a conditional show expression. ARGH! Well since I sit next to the extension guru, I asked him if he could tweak the extension and give me the ability to show/hide the extension. After a couple of minutes of tweaking, I was in business. I was able to hide the video player when the “View all video tags” button was selected by creating a variable named vShowTags. When the “View all video tags” button is selected, there is an action that sets the vShowTags variable to YES. When that happens, the webpageviewer extension is hidden. And when the user selects to close the popup another action is triggered that sets the vShowTags variable to NO which allows the webpageviewer extension to show on the page.There are many ways to accomplish desired tasks in QlikView. The challenge is sorting through all of your options until you find the best solution. Click here for the zip file that contains the QVW, Technical Brief and the extension object used in the creation of the Video Player demo. You can also see the Video Player demo on demo.qlik.com Happy Qliking! ...View More

Oh, glorious were the days! Businesswise we were perhaps not yet so successful, but product maturity made giant leaps forward. QlikView 3 was a very good product packed with new features that made life easier. Both developers and users loved it. It was easier for prospects to evaluate it and it was easier for us to sell than previous versions. QlikView 3 was released in the spring of 1997, in time for the yearly IT exhibition CeBIT in Hannover. In version 1 and 2 we had focused on the UI (user interface) – on presenting data in such a way that a user could explore it and learn from it. For QlikView 3, we had changed focus slightly. We now worked more on simplifying the data loading process and the development of the document. We also made it easier for people to try out the product by introducing a 15-day evaluation license. As a result, more and more people started developing applications with QlikView. And it became much easier to convince prospects and partners to join us on our journey. One major change in QlikView 3 was the Automatic Outer Join. Already in QlikView 1 and 2, you could have a data model with several tables. But the associations between the tables were like inner joins and no analysis was possible for values that lacked corresponding records in a neighboring table. For instance, customers that had no orders in the Orders table were always grayed out. But with QlikView 3 it became possible to select such customers. This is a feature that we today take for granted, but it was not there in the beginning. Another change in QlikView 3 was that the scripting language developed immensely. We introduced a number of new script constructs. Join, Concatenate, Crosstable, Intervalmatch, resident load, preceding load, Group by, Peek, Previous, and Variables the way we know them today, all arrived in QlikView 3 – as did the text file wizard and the possibility to load files over the internet. You could now make fairly complex data transformations right in the QlikView script. We also introduced several new sheet objects in QlikView 3 (line chart, scatter chart, multi box, straight table and table box) as well as new property dialogs – one for user preferences, one for the sheet, and one for the document itself. And you could right-click on the sheet and get a float menu. It became a whole lot easier to navigate around all the product settings. On top of all this, we released French and German language versions of QlikView, as well as a Macintosh edition. Yes, we had a Mac version of QlikView! However, we didn’t sell much of it and since it meant a lot of extra work for R&D, both in terms of development and test, we discontinued it one release later. And – most importantly of all – QlikView 3 was extremely stable. As a curiosity, QlikView 3 also had one of the best error messages ever: “Time Travel is not allowed!” Anyone can figure out when this was used? HIC Further reading on the Qlik history: A Historical Odyssey: QlikView 2 A Historical Odyssey: QlikView 4 and the first Server ...View More

How normalized should the QlikView data model be? To what extent should you have the data in several tables so that you avoid having the same information expressed on multiple rows? Usually as much as possible. The more normalized, the better. A normalized data model is easier to manage and minimizes the risk of incorrect calculations. This said, there are occasions where you need to de-normalize. A common case is when the source database contains a generic master table, i.e. a master table that is used for several purposes. For example: you have a common lookup table for customers, suppliers, and shippers. Or you have a master calendar table that is used for several different date fields, e.g. order date and shipping date (see image below). A typical sign for this situation is that the primary key of the master table links to several foreign keys, sometimes in different parts of the data model. The OrganizationID links to both CustomerID and ShipperID and the Date field links to both OrderDate and ShippingDate. The master table has several roles. The necessary de-normalization in QlikView is easy. You should simply load the master table several times using different field names, once for every role. (See image below). However, loading the same data twice is something many database professionals are reluctant to do; they think that it creates an unnecessary redundancy of data and hence is a bad solution. So they sometimes seek a solution where they can use a generic master table also in the QlikView data model. This is especially true for the master calendar table. If you belong to this group, I can tell you that loading the same table several times is not a bad solution. Au contraire – in my opinion it is the best solution. Here's why: From the user’s perspective it is confusing to have an unspecified “Date” field if there are several dates in the data model. For example, the user will not understand whether the date refers to order date or shipping date. Without loading the master calendar several times, it will not be possible for the user to make selections that place simultaneous restrictions on several date fields, e.g. “show transactions where the order was placed in September and the items were shipped in November”. In fact, loading the same table several times in QlikView is no stranger than doing it in SELECT statements using aliases, e.g., SELECT OrderID FROM OrdersINNER JOIN MasterCalendar AS OrderCalendar ON Orders.OrderDate=OrderCalendar.DateINNER JOIN MasterCalendar AS ShippingCalendar ON Orders.ShippingDate=ShippingCalendar.DateWHERE OrderCalendar.Month=9 AND ShippingCalendar.Month=11 In SQL you would never try to solve such a problem without joining the master table twice. And you should do the same in QlikView. So, if you have several dates in your data model – load the master calendar several times! HIC PS. But if you still want one common date field, you should create a Canonical Date. ...View More

One of the common business requirements when analyzing the data is limiting the dimension values on a chart. For instance, limiting the chart to the top 10 sales people, or showing only the products that make up 80% of sales. QlikView 11 dimension limits functionality enables the business users to easily create these logics on the charts.This functionality provides consistency across QlikView charts and more importantly better performance. Prior to QlikView 11, some charts in QlikView could display totals at the expression level, some can limit the number of dimension values to display and some can display an ‘OTHERS’ dimension value to catch those values omitted by the limits. With QlikView 11 dimension limits feature, these functionalities are consistent across chart types. Also, the conditions that are used on the dimension limits are calculated at the engine level, providing better performance and calculation time.The functionality offers four main options:Limits: This option restricts the dimension values displayed on a chart based on flexible criteria. The criteria are evaluated with the first expression of the chart. It is possible to restrict the dimension values by the largest, smallest, first, greater than or less than an exact value or relative the total.Global Grouping Mode: With this option, it is possible to treat the dimension values as either ‘local’ to the current upper dimension level in the chart, or ‘global’ to be evaluated across all instance of the upper level dimensions. This option is only applicable to the second or lower level dimensions on a chart. Show Total: This option enables the display of subtotals at the dimension levels.Show Others: This option displays an ‘OTHERS’ dimension value to catch those values omitted by the limits.Each option has a separate set of applications and can be used in different permutations. QlikView 11 dimension limits functionality makes it easier for the business users to customize the QlikView apps based on their own analysis needs with just a few clicks! ...View More

A popular topic in interactive design is that of scrolling. When is it ok to scroll? Is it ok to scroll? Will people know they can scroll? What information should be above the fold?Above the foldWhen web design began the designers were mostly trained print designers since interactive design was a new field and "web" designers didn't exist. The concept of "the fold" is one that print designers imported from newspaper design. The goal of designing "above the fold" was to keep the most important headlines and images in the top half of the newspaper so when it was folded in half at the newsstand the most enticing information would be visible and passers-by would stop and buy the paper.With the internet the concept of designing web pages with the important information above the fold, keeping key information viewable without scrolling vertically, was met with a new challenge: monitor variety. When a publisher produces a newspaper every customer gets the same sized paper. The content that is above the fold for one reader is the same for every reader. With web design the variety of monitors, browser chromes, and resolutions is so diverse that there is no standard height for where the fold begins. The fold on my phone is alot different than the fold on my 27inch desktop monitor. The ipad has not one but two folds: one in portrait orientation but a shorter fold in landscape orientation. Ignore the foldPeople's fear is that important information will never be found if it is below the fold. That somehow people (not themselves of course) don't know how or when to scroll to find additional information so the solution must be to cram as much content at the top of a page as possible. This is ridiculous. Usability tests continue to prove that not only do users scroll but scrolling can actually improve the user experience. If people feel they are on the right track they will continue to scroll for content.Advice:Design pages that are legible and attractive. If your application is well designed it encourages people to explore the document and they will scroll on their own.Vertical scrolling only. While users can scroll vertically as well as horizontally, generally speaking a page should do one or the other and people prefer vertical scrolling. Vertical scrolling is a more standard method of navigating content as well as the simple fact that most scroll wheels move vertically and not horizontally. Vertical scrolling is just easier.Monitor Resolution. To avoid horizontal scrolling you should know your intended audience. I use analytical data to find the largest number of users with the lowest common resolution and design for that. 1024x768 is still a decent standard size as well as being the resolution of the ipad in landscape orientation. ...View More

Of the 2 ½ years I have been with QlikTech, I have never had to use the IntervalMatch prefix in any of my scripts but I recently found out how powerful it can be. I was tasked with creating a Profit and Loss (P&L) statement in QlikView based on the format outlined in this Excel file.The “s” in the Exec P&L Level column indicates blank rowsThe “c” in the Exec P&L Calculation column indicates rows where a calculation needs to be performedThe Exec P&L Heading column stores the headings that are to be used in the P&L statementThe ExecPLStart and ExecPLEnd columns indicate the range of rows that are to be summedWho would have thought something as simple as IntervalMatch would solve my problem? I have always said that I learn something new about QlikView every day and this day was no different. I was able to load my master account information, my account balance information and then perform the IntervalMatch, using the reporting code field from the master account table, to get everything in sync so that the correct rows were summed in the P&L statement. Then all I needed to do was create my chart (a straight table) in QlikView. In the end my chart looked like the image below and I was quite pleased.I wrote a technical brief about how I completed this task. I know this is just one of the many ways IntervalMatch can be used. I am sure in the future I will learn some of the other ways. ...View More

There is a little known function in QlikView that hardly anyone uses and that doesn’t do very much, but still has a tremendous impact on many of the calculations made in QlikView. It is the Only() function. It returns the value of a parameter – but only if there is just one possible value. Hence, if you have a one-to-one relationship between the chart dimension and the parameter, the Only() function returns the only possible value back. But if there are several values, it returns NULL. The Only() function is an aggregation function, which means that it uses many records as input and returns one value only. The Sum() and Count() functions are examples of other aggregation functions. Aggregations are used whenever you have a group of records and need to show only one value representing all records. When you think about it, QlikView uses aggregations in virtually all calculations: The expression in a chart, in a sort expression, in a text box, in an advanced search and in a calculated label are all aggregations and cannot be calculated without involving an aggregation function. But what if the user enters an expression that lacks an explicit aggregation function? What does QlikView do then? For example, if the sort expression is set to “Date”? Or if there is an advanced search for customers using the expression “=Product='Shoe' ” (the intent is to find customers that have bought this product)? This is where the Only() function affects the calculation without the user knowing it; if there is no explicit aggregation function in the expression, QlikView uses the Only() function implicitly. Hence, in the above cases, “Only(Date)” is used as sort expression and “=Only(Product)='Shoe' ” is used as search criterion. Sometimes the new expression returns a result that the user does not expect. Both the above examples will work fine for cases when there is only one possible value of Date or Product, but neither of them will work for cases when there is more than one value. Therefore, when you write expressions you should always ask yourself which aggregation you want to use, or: Which value do you want to use if there are several values? If the answer is that you want to use NULL to represent several values, then you indeed want to use the Only() function and you can leave the expression as it is. But if you do not know the answer, then you should probably think again. For numbers, you probably want to use Sum(), Avg() or Min() instead and for strings you may want to use Only() or MinString(). For debugging you can always use something like, “Concat(distinct <Field>, ',')” and analyze the result. But you should not leave your expression without an aggregation function. HIC Further reading related to this topic: It’s all Aggregations Use Aggregation Functions! ...View More

I've been a designer for about 10 years, 8 of which I've been designing web sites. The fancy buzz-word way of saying you work on websites is to say that you work in "User Experience Design." The user experience is how someone (a user) might interact with something (a website, an ATM, or in this case QlikView). You consider what troubles a user might encounter, what might be helpful, how they will accomplish certain tasks, etc. From Amazon to Zappos, User Experience Design is the (not so) secret ingredient that separates the bad experiences from great experiences with loyal customers.So what makes User Experience Design work? One of the keys is to be empathetic, to put yourself in the shoes of the user and design for them and not yourself. You design for and with others, that is you don't do it alone. Designers are just one group of people who take a website from conception to completion. Information Architects, Writers, Designers, Developers, and Usability Engineers all contribute to the process. People with specialized disciplines doing what they do best to make great experiences. If you aren't lucky enough to have a team of bright UX professionals, it probably means you will have to become a "jack of all trades" and take on all of these roles yourself.Never fear. As a guide I've written the attached Technical Paper that walks you through how you can apply the iterative UX waterfall design process to developing QlikView applications. There is a lot of overlap between designing a website and designing a QlikView application because at the heart of both is the user experience. Designing for what is best for the user is key no matter what the final product.Next Steps? Start small. I would recommend trying these ideas on a smaller, easier project first and then apply what works for you to larger projects over time. ...View More

Since we introduced the first version of the QlikView SAP connector 6 years ago, many of the QlikView customers leveraged the power of QlikView with the SAP data. Through QlikView’s unique, in-memory associative technology, they are able to make SAP based business decisions with the speed of the business.The key enabler of the solution is the certified QlikView SAP Netweaver® Connector, which can extract data from SAP® R/3®, mySAP™, SAP BW, and BEX queries into QlikView. The extracted data can be combined with non-SAP data in one QlikView application providing a 360 degree view of the business.Let’s take a look at a business case and understand how the QlikView SAP Netweaver® Connector would help. Assume a company has various SAP Netweaver data stores and its users would like to do sales analysis across their business. With SAP Netweaver® Connector they can connect and extract data from;SAP BW/BI Business Explorer Query for sales informationSAP Data Store Object (DSO) for additional order values and shipping weightsAn SAP Query to gain insight into sales organization detail metricsAn SAP R/3 Table so they can integrate the company details into Google mapsAn SAP Extractor for customer itemsAn SAP BAPI for business area lookupsAn SAP Report for customer payments They can then create visual and flexible analysis with this data in one single QlikView application which can also integrate data from external non-SAP data sources.QlikView provides pre-packaged QlikStart templates for SAP R/3 as well as QlikView SAP data dictionary and QlikView script builder applications. These apps would give a good head start for IT by delivering the background database information of how tables within SAP interact. It would also enable the business users to quickly realize their own data in ways they may not have envisioned before.Last month we introduced a new version of the QlikView SAP Netweaver® Connector (SAP Netweaver version 5.7) with even more capabilities. If you would like to have a jump start to do Business Discovery with your SAP data, you can download the QlikStart templates from QlikCommunity. ...View More