QlikView has many useful and sometimes underused functions that can make your life as a developer a lot easier. One of these is CONCAT(). This should not be confused with the CONCATENATE script keyword.In its simplest form, CONCAT() is used to string together values/words/selections in to one string. However, it can be utilized in a number of ways to help you solve different problems.All of the examples in this post are based on the data in the table below.....I have shown a few simple but useful examples below to hopefully help you in your everyday QlikView development!Simple String ConcatenationAs mentioned earlier, the function lets you string together a list of values. These values can be hard coded or driven my selections/data. =CONCAT(MyColumn,',') =CONCAT(DISTINCT MyColumn,',')This simple concat statement would string together all of the possible values from the column MyColumn. You may wish to add the DISTINCT keyword. This would ensure that each value is only displayed once in the string.ABC,DEF,GHI,JKL,MNO,PQR,STU,VWXWhen using a simple concat, you have the option to add a sort weight to the function to order the string values by a column of your choice. In the example below, I have added the date column to sort the values…..=CONCAT(MyColumn, ',', Date)Result: JKL,VWX,GHI,ABC,STU,PQR,MNO,DEFConcat() within an expression/set statementThere are occasions when you want to pass a selection of values to a set statement. To do this I would need to add some single quotes to the string so that the CONCAT() function returns e.g. 'JKL','VWX'. But you cannot have the single quotes as they are, since they then would be interpreted when the Concat is evaluated instead of when the set expression is evaluated. Instead I use the Chr() function:=CONCAT(Chr(39)&MyColumn&Chr(39),',')I can then pass this concat statement to the inside of an expression…=Sum({<MyColumn={$(=CONCAT(Chr(39)&MyColumn&Chr(39),','))}>} Value)In most instances, this technique would be used where data islands are present. It lets me pass values to an expression that will not affect any part of the data model as the data island table is not joined to it.Concat() in the ScriptConcat can also be utilised in the script to let you convert multiple rows in to one single column value, just as any other aggregation.Remembering the source data we have earlier in the blog. The result of script side CONCAT can be seen below...Using Rank() to influence the Concat()When you start to utilise other function in conjunction with concat() you can start to achieve to clever results. In this example, I have used Rank() to grab the top 3 performers (based on Value) and string them together.=CONCAT(IF(aggr(Rank(sum(Value)),MyColumn)<=3,MyColumn),',')Result: ABC,MNO,STUSo as you can see, there are a number of uses for CONCAT(). Let's if you can find more!For more information, please refer to the Technical Brief here.ABY. ...View More

One of the most common problems to solve in data modeling is that of time. How to assign month name and other time attributes to a date. The solution is called a master calendar.

...View More

We all use QlikCommunity when we need an answer for a QlikView question. We post a question and get an answer, usually in minutes, leveraging the expertise of 91,000+ QlikView users! QlikCommunity is the most-visited and active user community in the BI industry.I also use QlikCommunity. And sometimes not to get an answer for a question but to think about new ways of doing things by reading the answers on the existing posts. So I have been curious about the answers on calculating the accumulative sums in QlikView and did a search on QlikCommunity. There are many answers provided as expected. Here is one way of achieving it by using rangesum() and aggr() functions.It is a very common requirement to display the accumulative sum of a chart metric. The easiest way of doing this is to use the accumulate setting under the expressions tab of chart property. Figure 1. Accumulate option on chart propertyWith this setting, the values of the selected metric will be accumulated. But sometimes, the requirement is to have the first data point accumulated as well. For example, to show the accumulated sales for 12 months where the first month displayed should be the sum of the previous 12 months and the following months should accumulate on top of this value by adding one month at a time. One way of achieving this is to use the rangesum() and the aggr() functions together. Let us first look at Rangesum(). It is a range function that returns the sum of a range of 1 to N arguments Together with the above() function, you can calculate the sum of the last 12 months;rangesum( above( sum(Sales),0,12) ) This expression will return the sum of sales evaluated for each month as they appear on the 12 rows above the current row.The next step is to combine RangeSum() with the aggr() function to aggregate the sum of sales for the 12 months at the month level. sum( aggr( rangesum( above( sum(Sales),0,12) ),Month)) Using the Aggr() function, it is possible to calculate the accumulated sum for any other dimensions in addition to the time dimension. For example, the 12 months accumulated sum of sales by store can be calculated as;sum( aggr( rangesum( above( sum(Sales),0,12) ),Month, Store))Optionally you could also clear any selections on the month field as the calculation should reflect the accumulated sales for the previous 12 months, regardless of selections. This is achieved by using the set analysis “{$<Month>}”.sum( aggr( rangesum( above( sum( {$<Month>} Sales),0,12) ),Month)) One important thing to notice with this solution is the sort order inside the aggr() function. If the sort order is not correct, the aggregated sum of the 12 months would not be correct. QlikView always sorts the groupings (in this case the groups defined by the second parameter of the aggr() function) according to the load order. If the data is sorted by date during the load, QlikView will use this order and the aggr() function will show the correct totals for the desired time frame. In summary, the accumulation option on the expression tab is the simplest way of achieving accumulations, but the use of rangesum() and aggr() functions are alternative ways of accumulating the data in more complex situations. ...View More

QlikView 3 was a giant leap forward. 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. As a result we faced more and more demands from the real world: tools with which you can customize a document, tools for security, tools to save and re-use a selection, client/server, etc. Much of this didn’t exist in QlikView 3. So, for QlikView 4 we started to develop features based heavily on customer requirements. We had already experimented a little with macros in QlikView 3 but in QlikView 4 we did it properly. We introduced the first color coding in pivot tables so that our customers could mark negative numbers red. We made it possible to activate several objects and align them. We made it possible to load Excel files. We introduced control structures such as For-Next loops in the script language. We introduced Section Access to address security demands. We introduced bookmarks. We introduced a proper NULL value handling and we introduced the text object, so that customers could show also static information. We also had a new logo and a new icon. But there are two features that, more than any others should be noted as the principal features in QlikView 4: The chart engine.With QlikView 4, you could write any expression as an aggregation and QlikView would be able to evaluate it on the fly. This is true still today; you can have an expression with several arbitrary fields, where the fields reside in different tables, and then aggregate this expression. The aggregate can in turn be used in a new expression that uses other aggregations with fields from yet other tables, e.g., Sum(x*y)/Count(z). QlikView evaluates this expression correctly. This feature is unique to QlikView and is the core of one of QlikTech’s patents. The Server. The second major advancement in QlikView 4 was the client-server capability. It was then called QlikView Web Solution. Compared to the QlikView Server of today, it was rudimentary. But it was a first step and some of the structure is still the same. Application development, then, was done just as it is today: on a standalone QlikView. The file could then be copied to the server. The only client that existed then was a Java client talking directly to the server. The server had to have Microsoft IIS (Internet Information Server) installed and the client had to have Microsoft Java installed. This meant that in practice we only supported Microsoft environments, both on the client and on the server side. As I think back on it, it was really with QlikView 4 that we started our journey toward being an enterprise software product. HIC Further reading on the Qlik history: A Historical Odyssey: QlikView 3 A Historical Odyssey: QlikView 5 and the Rainbow Border A Historical Odyssey: The Doctors’ Special ...View More

If I say the word “Dashboard”, most readers probably think of a software tool that gives an overview of enterprise health, rather than of a control panel in front of the driver in a vehicle. One is in a computer, and the other is in a car, but they should play the same role; it should alert you when something is wrong. So where do we start when creating a dashboard for business users?I follow a D-A-R concept: dashboard-analysis-report. The first QlikView tab, the dashboard, tells users what is good or bad. It’s the “what” sheet. The next batch of tabs is for analysis and has graphs and tables which tell users “how” or “why” things are good or bad. The last part, the report sheet, has more detailed information such as invoice details, order details, employees’ shifts and so on where users can identify the exact action item to act on. So when creating a dashboard, it’s important to highlight the “what” in it. The first step is to identify the key metrics. The fewer the better. Don’t place 50 KPI’s to try to characterize the entire company in a dashboard, but rather the major ones that matter the most. Pick metrics that change every day if the dashboard is to be utilized daily. Secondly, make sure all KPI’s have a comparison. Human beings are trained to compare everything 24/7. If your sales figure is 3 million dollars, then so what? Is it better than yesterday’s number? You need to place a comparison number to indicate whether it is better or worse. Next, don’t use the color green when something is good. If you have 4 KPI’s (key performance indicators) and two of them are green and the other two are red, then people take it as 50% good, not 50% bad. You want users to focus on the ‘bad’ part so they can act on it. Only color-code what is bad, that is, red. Compare with a dashboard in a car. It does not alert you when something is doing well, but it is designed to alert you on the bad things. Think about the fuel light. It’s not lit in green when your car has enough fuel. Instead it alerts you when fuel is critically low. Then it comes on in red and you notice it right away. It grabs your attention and personally I even get nervous when I see it. Think about a usage of gauges. A big number with a small red color indicator will do a better job to alert users than a round gauge with both green and red in it.Bottom line: When designing a dashboard, make sure to keep it as simple as possible. When a user looks at it for 10 seconds and looks away, she or he should remember what was bad. Otherwise, it is not playing its fundamental role and is just pretty to look at.Executive Dashboard on demo.qlik.com. Here is a technical brief on this topic. ...View More

Authorization is about determining which data a user is allowed to see. QlikView has several different ways by which you can reduce the data so that the user only gets to see the data he is allowed to see.

...View More

Renaming fields in the script is something that all QlikView developers do, not only for creating links between tables, but also for making the sometimes cryptic database field names understandable for the users. So how do you best do that? The question seems trivial, but there are in fact a number of things to say about renaming fields.

...View More

The highly respected (fictional) scientist, Ian Malcolm, once chastised a group of scientists for being “so preoccupied with whether or not they could that they didn't stop to think if they should.” Ignoring the fact that this statement was made in regard to ferocious dinosaurs that would soon terrorize mankind, there is truth held within for how we approach technology.In this business we’ve all been guilty at one time or another of using the “more is more” approach in data visualization. All too often we marvel at something that dazzles without stepping back for a minute to consider the story that’s actually being told by the data. This is especially true with QlikView extensions.There are a plethora of JavaScript libraries that display data in exciting and dynamic ways. It flies here and there, spins, zooms, explodes, and distracts attention for a bit before the viewer attempts to decipher the information loosely assembled in the chaos. With extensions we sometimes jump at the opportunity to cram a hot, buzzy technology into QlikView simply for the bragging rights. Sometimes what is needed is something much simpler.When I began working on an extension to visualize medal counts for the Olympic Games this past summer, it was assumed that I would use a slick mapping UI similar to Google Maps or OpenLayers. These powerful mapping tools allow developers to perform a wide variety of complex geospatial tasks, taking into account curvature of the earth, map projections, tessellation, and a variety of other things that will induce sleep in the average human being.In order to achieve the effect of highlighting and coloring the different countries for this extension, it is not as simple as telling the Google Maps API to select country X and highlight it with color Y. These mapping tools do not have country or region boundaries prepackaged. Due to this fact, the actual longitude and latitude points for the boundaries of each and every country would need to be found and plotted as shapes on the map. Not only is this a LOT of data for JavaScript to handle, geographical data is not easy to come by.So, let’s take a step back. The question that needs to be asked is how much additional detail will users need? The data we’re looking to visualize is not geographic in a technical sense. We only need to display the countries of the world as concepts and entities, not as precise geographic objects. In this case, the precision offered by the mapping tool is completely irrelevant, as is the map itself beneath the plotted shapes. Why repurpose a complex and powerful mapping tool to draw a picture? Instead, why don’t we just, you know, DRAW A PICTURE?Fine, but how do we do that? Enter SVG. SVG (or Scalable Vector Graphics) is a form of vector imagery in XML format which draws shapes and lines as a series of paths. While this format is not supported by all browsers (ahem…Internet Explorer), fortunately there is a JavaScript library called Raphaël that will draw SVG paths in old and new browsers. Finding an SVG of the countries of the world was relatively easy, and from that point, I leveraged Raphaël in drawing the image.Rather than explain further in great detail how I coded and created this extension, what’s more important is to emphasize the need to consider the goals of our data visualizations. Just because we’re showing a map, must we use a mapping tool? Clearly not, and by drawing the world map as SVG, we used the appropriate tool. Our visualizations should yield to the data. If the data is trying to tell a story, we should get out of the way and let it tell its tale. For a more in depth and technical overview of the extension, please click here for a zip file containing the extension and a technical brief. ...View More

My latest blog post was on joins in QlikView (see To Join or not to Join). In it I claimed that you should avoid making joins in the QlikView script, if possible. This blog post is about a function that can help you avoid joins. It is about the function Applymap().

...View More

Actionable DataIt should come as no surprise that in order for raw data to break free from its deluge and become truly actionable - it must go through a number of steps before it is finally ready for decision makers. Steps can include various methods that access, combine and transform the data to make it meaningful for Business Discovery. Traditionally, methods that make data actionable can include a combination of data integration applications (ETL) and/or QlikView data-readiness procedures (QlikView Script). These methods in turn prime QlikView’s powerful associative in memory data layer used by all QlikView deployments. QlikView and knowledge workers are then responsible to turn that actionable data into useful information to help drive decisions.Metadata in Action With the introduction of QlikView Expressor, data provisioning for QlikView deployments has been redefined. Whether you are simply accessing data from a warehouse or combining data from disparate sources, QlikView Expressor will provide both actionable data along with actionable metadata as you develop. This makes managing data for QlikView more efficient, less prescriptive and will ensure a higher degree of data confidence and reuse across all QlikView applications. Metadata is typically defined as data about data. Active metadata on the other hand, can be defined as "metadata in action". Traditional Business Intelligence metadata is mostly static and limited to only describing a few properties about the data it references - such as sources, data types, column names, length and format. QlikView Expressor metadata is not static, it’s active. It describes common properties about the data while also actively respecting rules that have been defined on its attributes. Let’s take a simple example using a string column named ZIP used to store a 5 digit +4 zip code. Not only can the metadata describe the attribute using a common business term such as PostalCode, but it can also specify a minimum and maximum string length constraint to ensure the data length only falls within the specified range. Furthermore, it can provide a pattern match rule using a regular expression such as ^\d{5}-\d{4}$. This will ensure that the data flowing to QlikView will absolutely match the XXXXX-XXXX pattern. - What if the data fails the rules? A choice to set a corrective action to use a default value or even redirect the record to be captured is available, allowing more control on where and how the data is processed. Other examples can include using an allowable list of values, setting rounding / min / max / precision / scale / constraints on numeric values, date range validation, date formatting and string padding / truncation. Using active metadata with QlikView Expressor is just one part of the overall QlikView Expressor solution. Combined with other key capabilities, you will discover how QlikView Expressor can help simplify your QlikView application development and create a reusable environment that reduces development time and speeds up your deployment efforts. Come back to this blog often as I will be covering these capabilities and much more in future articles. Figure 1: Managing Active Metadata with QlikView ExpressorMike TaralloSenior Product Marketing ManagerQlikView Expressor ...View More