As a QlikView developer I am often asked to load images into QlikView. In some instances the images are associated to other data fields and in other instances they are to be loaded in to the application to help convey a message. For example, let’s say that you need to bring in flags of countries that are to display when the corresponding country is selected and you need to display an icon that indicates whether sales for a country are above or below a predefined threshold. It sounds like an easy enough task; so how do you do it?Well, QlikView offers the developer the Bundle function that can be added to the Load statement. Bundle Load statement allows the developer to load the image files directly into the QlikView application for portability. The Bundle Process is a very simple scripting process. The syntax for the Bundle Load should look like this:The image file should contain two fields:The first field is an identifier such as an index number, image name, a key field that associates the image back to other data files, etc.The second field should contain the path to the image.This file contains the CountryID so that I can associate the flags with the countriesThis file contains just an image name because I am just using these icons as a reference and they are not associated to any fields in other files. Once the images are loaded into QlikView we can now reference them using the INFO() function and a standard IF Statement:I am using the INFO() function to display the flags so whenever a Country is Selected, QlikView will know to go grab the corresponding image based on the CountryID. If we needed to, we could do the same thing with the symbols by simply adding a list box for Image Name and selecting an image from there. For this example though, I am using a standard IF Statement to display the symbol for the country sales.Here is what it looks like when the user selects a country. The Info() function displays the image associated with CountryID =1 and because Sales were above the threshold the green square is displayed. One note of caution, when images are loaded into a QlikView application using the Bundle Load statement, both the amount of RAM and the size of the application increase so consider both the size and the amount of image files before deciding to use the Bundle LOAD statement.I wrote a technical brief that outlines these steps in more detail. You can access it here.Happy Qliking! ...View More

A common situation in Business Intelligence is that an organization uses a financial year (fiscal year) different from the calendar year. Which fiscal year to use, varies between businesses and countries. [Wikipedia] But how would you solve that in QlikView?

...View More

When using QlikView, sometimes we are faced with challenges such as how do we transform an Excel spreadsheet that looks like the image below into a trial balance chart that allows us to see the monthly activity for each company and account.Well using various functions and features of QlikView such as:• CrossTable Load of Excel spreadsheet• IsNull() function• RowNo() function• Peek() function• Date functions• Preceding loadWe can create a trial balance sheet that looks like this:Creating this chart involves loading the Excel spreadsheet into QlikView in a format that works best for us using a CrossTable load. After sorting this data by Company Number, Account Number and Month Year, we are ready to create the Opening and Closing fields that we will need for the trial balance chart. Using the RowNo() and Peek() functions, we are able to create the opening and closing balance fields for each account number on a monthly basis allowing the user to see what the activity was like during any given month. Here is a snippet of what that script looks like:You can view the step by step details of how the trial balance chart was created in this technical brief.Jennell ...View More

Already when we were selling QlikView 3, we had received a fairly advanced customer demand from pharmaceutical companies. We solved it. And here’s how we did it. But first some background: In the pharmaceutical industry, the sales reps are not the ones that sell the products. Instead, they visit physicians and demonstrate one or several products. Days, weeks or months later, the physician prescribe the demonstrated medicine to a patient, and the actual sale takes place when the patient buys the drug at a pharmacy. The demand on QlikView was to show pharmacy sales data, not only per physician, but also per physician visited once, twice, three times, etc. In other words, the physicians should be grouped by number of visits, and this number should be used as dimension in a QlikView chart. A nested aggregation. For QlikView 4 we had a solution for this. Well, solution is perhaps not the right word… There was a kludgy, hidden feature with which you could use a count of a field as dimension. Internally it was called the Doctor Controls. First you had to enable this feature in the list of hidden settings. Then you could create your chart: Count(Visit) per Physician. After that, you needed to enable the “Display Result Count” in the chart: The left graph shows graph as-is – without the “Display Result Count” enabled. It shows the distinct count of visits per physician, just as the dimension and expression of the chart are defined. But, by turning on the “Display Result Count”, the chart transformed into the right graph: The displayed dimension was now the equivalent to an Aggr(Count(Visit),Physician) and the displayed expression was Count(Physician). We solved a customer’s problem at the time, but this was not a good, long term solution. And it was nothing we wanted to support. Instead we wanted a general solution for nested aggregations. Six years later – after much thinking – Håkan (the Inventor) came up with the Aggr() function for QlikView 7. It is a general function for nested aggregations that made the Doctors’ Special redundant. But like a relic from the past, the Doctor Controls setting can still today be found in the QlikView 11 list of hidden settings. It doesn’t affect anything - I hope. HIC Further reading on the Qlik history: A Historical Odyssey: Quality - Learning - Interaction - Knowledge A Historical Odyssey: QlikView 1 ...View More

Are you stuck with a complex data model? Do you need some tips to resolve the linkage of different fact tables? Does your model have data knots, circular references or synthetic keys? If you have ever faced any of these situations you've probably heard about Link Tables and Concatenate as methods that can help you to put some light in your data model. It’s hard to find a general rule to determine when you should Concatenate rather than joining tables by a Link Table so today we will highlight the main differences of both methods by reviewing usage case scenarios. Lets start with some basic examples:Concatenate“This statement forces concatenation with an existing named table or the latest previously created Logical Table. A concatenation is in principle the same as the SQL UNION statement, but with two differences: first that Concatenate prefix can be used no matter if the tables have identical field names or not; and secondly that no removals of identical records are made” Source: QlikView helpMost basic example of Concatenate usage could be when you need to merge two or more tables that have identical structures, let say you have a historical data warehouse with sales from 2005 to 2012 and then another table with 2013 sales live in you transactional database. To create a visualization of sales trends over the years you will want to have everything normalized in one fact table, Sales table, with the data from 2005 to 2013.Link Table(s)“(…) a junction table is a database table that contains common fields from two or more other database tables within the same database. It is on the many side of a one-to-many relationship with each of the other tables. Junction tables are known under many names, among them cross-reference table, bridge table, join table, map table, intersection table, linking table, many-to-many resolver, Link Table, pairing table, pivot table, transition table, or association table. (…).”Source: Wikipedia This definition is mostly about relational SQL databases so if we adapt it for QlikView it could be something like the following. Link Table: It´s a table that contains common fields from two or more tables (within the same database or not). Easy, right?The most common scenario for using Link Tables will be to replace synthetic keys and to avoid circular references by joining two or more fact tables against a common set of dimensions. See much more in the attached files.AMZEnjoy Qliking! ...View More

Last month QlikTech released QlikView Expressor 3.9 which introduced some key features that not only strengthen data governance practices within a QlikView deployment, but also enable developers to extend QlikView Expressor functionality. Enhancements include:Conformance Mapping - enables consistent naming of attributes representing a given data element. It reduces the proliferation of unnecessary variations that can cause inconsistent results in data processing. Conformance Mapping makes it simple to unify the attributes mapped to fields in a Schema.Metadata Analysis Search and Usage - helps locate QlikView Expressor artifacts (dataflows, schemas, types, connections, attributes, fields, etc.) within workspaces, projects and libraries that are used to manage and prepare data for QlikView and other data targets. The Extension SDK - enables a set of toolkits and wizards that provide a framework with common functions for developing Extensions for QlikView Expressor.In this article I will briefly cover the QlikView Expressor Extension SDK and provide a supporting reference with examples that demonstrate how it can be used. (You can learn more about these features in this Technical Brief.)The Extension SDKTypically, software packages provide a method to extend their functionality using a development kit or API. This allows a developer to build in new capabilities that otherwise would not be available in the out-of-the-box software. The QlikView Expressor Extension SDK enables a set of toolkits and wizards that provide a framework with common functions for developing extensions for QlikView Expressor. With extensions, developers can provide clients with tools to integrate data from specialized sources and perform specialized transformations on data. An extension’s operators, connections and schema integrate into the QlikView Expressor Desktop interface and seamlessly work in conjunction with other standard, enterprise artifacts and operators.The Extensions SDK provides a set of compilation, utility, and data type conversion functions using built-in Datascript Modules (DSM). DSMs are the standard QlikView Expressor scripting module for Datascript, based on the Lua open-source scripting language. Extensions built on these common functions add functionality to QlikView Expressor Desktop to support custom data sources and specialized transformations.Figure 1 - The Extension BuilderWhat is a QlikView Expressor Extension?A QlikView Expressor Extension is a plug-in (or add-on) that adds new Operators and Metadata artifacts to those already included in the core QlikView Expressor product. Extensions can provide read,write,transform and work-flow-like operations that are not available with the standard operators and artifact types. Once developed they can be easily packaged and distributed with the QlikView Expressor Extensions Manager.Figure 2 - Custom Operators for reading files from a directory or FTPThe Extension SDK TutorialWant to give it a try? Check out this tutorial and sample project to learn how extensions are built using QlikView Expressor.NOTE: Please make sure you have QlikView Expressor 3.9.1 installed.Samples available in the project include:Reading Fixed Width FilesReading delimited files from a directoryReading a data file via FTPEncrypting and Decrypting Data with a custom transformRegards,Michael TaralloSenior Product Marketing ManagerQlikView and QlikView Expressor@mtarallo ...View More

When we start working on a new project, the customer requirements form the guiding rails for us to begin the process of designing. As we start brainstorming and throwing out ideas, we begin to fill the empty spaces with uncertain experiments. We then start making tiny decision which we stitch together part by part in trying to complete the big picture. Initially to get started with drawing an outline of the project, we naturally tend to ask ourselves the basic questions of why, what, where, how. As the project progresses, we come across unforeseen hurdles, feedback, opinions and try to work our way through it to achieve the big goal. However, in the process of doing so, there are very strong chances of digressing from the main purpose of the project. In trying to put the small pieces together, and focusing on how to make it happen, we sometimes tend to lose the purpose of why we are doing what we are doing. This is when the question “Why” always comes handy to validate our work.A design can never be a solution if it doesn’t fulfill the underlying purpose of its existence. Asking the question “Why” each time we complete our little milestones, that we set for ourselves, can do wonders to the final solution. In the book called “The Shape of Design”, Frank Chimero points out - “Our mistake was the same as that of the creative person who places too much focus on How to create her work, while ignoring Why she is creating it. Questions about How to do things improves craft and elevates form, but asking Why unearths a purpose and develops a point of view. We need to do more than hit the right note. The creative process, in essence, is an individual in dialogue with themselves and the work. Why is usually neglected, because How is more easily framed.” The process of design is most successful when it is an iterative process rather than a linear one. Asking the question “Why” at every stage to validate your work can not only help in creating a strong argument for your work but also help in delivering a legitimate story. So, a reality check by asking the question “why” at every stage of the process can help in creating the solution that one set out to provide in the first place. ...View More

For QlikView 7 we developed a number of features that would enable developers to make even more advanced applications. One such feature was the Buffer prefix – a prefix that you could put in front of a Load or a SELECT that would store the data on the local machine and automatically use the local data when appropriate. With it, you could e.g. load data from a slow ODBC connection just once a day and for other script runs use the buffer. The buffered data needed to be compact and something that QlikView could load fast. So, the QVD file was invented to solve this need. Today, hardly anyone uses the Buffer prefix, but QVD files created with the Store command are often used. Another new feature was the Aggr() function. Already for QlikView 4 we had a solution for having an aggregation as dimension. Well, solution is perhaps not the right word… There was a kludgy, hidden feature with which you could use a count of a field as dimension. Internally it was called the Doctors’ Special. We solved a customer’s problem at the time, but we were not satisfied: we instead wanted a general, good solution for nested aggregations. Six years later – after much thinking – Håkan (the Inventor) came up with the Aggr() function. It is a general function that can be used both as dimension and as measure. It can be nested and it can internally use any aggregation. In other words: it is a general function for nested aggregations. A by-product was the calculated dimension, which was necessary in order to use the Aggr() as dimension. The Intel Itanium processor was first released in 2001, but its sales had still not picked up when QlikView 7 was released four years later in 2005. Instead, a new kid appeared on the block: The AMD X64 architecture. The X64 turned out to be both cheaper and faster than the Itanium, and as a consequence it sold better. We already had an Itanium edition of QlikView and we realized that we also needed an X64 edition. Porting the code was straightforward and QlikView 7 was now shipped in three editions: X86, IA64 and X64. QlikView 7 also brought the calendar object, the block chart, the box plot, the expression overview, the variable overview, the alerts and the reports. As a curiosity, I can also mention that QlikView 7 was the first version with scroll bars for the sheet. None of the previous versions had had this. We had always had the opinion that scroll bars would steal screen space and not add any relevant functionality. Instead we had a zoom function so that you could fit the work area to the screen. But - it’s never too late to change one’s mind… HIC Further reading on the Qlik history: A Historical Odyssey: QlikView 6 and Multi-threading A Historical Odyssey: QlikView 8, Ajax and Set Analysis A Historical Odyssey: The Doctors’ Special ...View More

NULL is not a value. It is a lack of value. It is a placeholder that marks nothingness. So how do you search for NULLs? How do you find the customers that didn't buy product X? Or, how do you find the users that didn't log on this month? There is no search string that matches NULL and even if there were, you can’t select NULL. NULLs cannot be selected explicitly, so to find the records with NULLs, the selection must always be made in another field. In the example of customers not having bought product X, it means that the Product field for some customers is NULL. Hence, you need to select the customers for which the Product is NULL. In other words – you need to make the selection in a field other than where you have the NULL. And here’s how you do it: Set your selection criteria the normal way. Use Select Excluded on the field where you want to negate the selection For example, if you want to find customers that have not bought Basket Shoes, then you should first select Basket Shoes from the Product list box. Then you will in your Customer list box have the customers that indeed bought Basket Shoes. But the grey customers are the ones you are looking for. So, right click, and Select Excluded. Voilà! The second example was how to find users that have not logged this month. Analogously, you first select the month and then you negate the selection by using Select Excluded on the User list box. A third example could be that you want to find the customers that have not bought any product at all. Then you should first right-click the products and Select All. This will maybe not change very much, but it will exclude the customers that never placed any orders. In other words: These are now gray and can be selected using Select Excluded. A final example could be that you have a combination of criteria, e.g. you want to find customers that have not bought any shoes in the last few months. The method is still the same: Select relevant products and select relevant time range. The possible customers are the ones that have bought of the products in the time range, and the excluded customers are the interesting ones. Select Excluded! However, when you have a combination of selections, QlikView doesn’t always remove both of the initial selections when you select the excluded values, so to get it right you should combine it with a Clear Other Fields. A good, user-friendly solution is to put both commands in a button that you label Select Excluded Customers. If you want to read more about how to manage NULLs in your QlikView application, you should read this Technical Brief. HIC ...View More

I was asked recently if I could produce a standard control chart or SPC chart in QlikView. So I thought I would share this on the blog.SPC and Control charts are ways of tracking if a process is "in control". If you take a look at the Wikipedia entry below you can see that Control charts have been in existence for nearly a 100 years, and are still used today, especially with the introduction of 6 sigma in 1995."The control chart was invented by Walter A. Shewhart while working for Bell Labs in the 1920s. The company's engineers had been seeking to improve the reliability of their telephony transmission systems. Because amplifiers and other equipment had to be buried underground, there was a business need to reduce the frequency of failures and repairs. By 1920, the engineers had already realized the importance of reducing variation in a manufacturing process. Moreover, they had realized that continual process-adjustment in reaction to non-conformance actually increased variation and degraded quality. Shewhart framed the problem in terms of Common- and special-causes of variation and, on May 16, 1924, wrote an internal memo introducing the control chart as a tool for distinguishing between the two."(https://en.wikipedia.org/wiki/Control_chart) In this QlikView example of a control chart I have tried to give you a number of reference lines and also used the Standard Deviation functon to drive our control limits. The chart itself is dynamic and will react to any selections made wihtin the application. This will let you monitor your process control by any dimension you add such as Country, Region, Plant and also specific products and time periods.You can find the application and document here. ...View More