Authentication and Authorization are two important concepts in securing any application. Let’s start with some simple definitions. Authentication makes sure that the person accessing the system is the person he says he is. Authorization only lets you access information and complete actions that you are allowed to, based on your identity.In QlikView, these are two distinct activities performed independent of each other. This often creates some confusion and configuration errors, so let me explain how it works. When a user gets access to QlikView it is always done in these four steps:One of the most common misunderstandings around this is what services are part of what step in the process.The first two steps covering authentication are handled by the web layer (i.e. QVWS or IIS). The third step is achieved by the web layer transferring the identity to the QlikView Server using the QVP protocol. The fourth step is authorization and is handled by the QlikView Server using groups resolved by the Directory Service Connector.There are some big benefits to this approach:QlikView does not have to store passwords; these are stored by an identity provider such as LDAP or AD.Normal procedures for user management can be applied, which enables that adherence to security policies are maintained.It is possible to customize authentication without affecting authorization, which gives us the option to use external identify providers such as Google and Salesforce.All Authorization is done in the backend, making it easier to protect.The role of the Directory Service Connector in the flow is somewhat blurred by the fact that almost all QlikView components use it. The web layer, QlikView Server, QlikView Management Service, and the QlikView Publisher all use the Directory Service Connector for different things.Most QlikView components use the Directory Service Connector for authorization or to get information about users except if custom users are used. If you use custom users, these get authenticated towards the Directory Service Connector, which in this special case stores identity and passwords for the users.Remember, as a rule of thumb: the front end components handle authentication and the backend components handle authorization. I hope this help gives you a clearer picture of how QlikView handles authentication and authorization and which components are used in which part of the flow.Have further questions you’d like me to answer? Leave me a comment! ...View More

Who you are is the product of all of the experiences you have had, and not had, throughout your lifetime. Nobody operates in complete isolation. Everyone is influenced by sources outside of themselves. We take those experiences and internalize them with our other memories in our own way but ultimately everything you come in contact with serves as material for the future you. So it stands to reason that new ideas & creativity are also the result of taking existing ideas and transforming them.People frequently talk about ideas/people as being "totally original," but the truth is that originality is rather unoriginal. People with seemingly totally new ideas are really just the result of taking existing concepts and bringing them together in new ways. Perhaps you can identify the original source material, perhaps you can not, but everyone is influenced by ideas outside of themselves and nobody creates something entirely new.The 4 part video series Everything is a Remix is a fantastic exploration of this in action. From music, to film, to mechanical invention everyone is influenced by the work of others. ...View More

You wake up in the morning and head down to the living room; faintly you hear a lingering “hohoho” from the chimney. Santa was here – and he left us something wonderful!Santa is early this year, not only did he bring us a new and shiny service release for QlikView he also included a free to use mapping extension!When you install the latest version of QlikView, http://www.qlik.com/download/, we now ship an Example Extension Object that makes use of OpenLayers and MapQuest.So how do I make use of this sweet nectar you say? Let me take you on a journey and explore some mapping possibilities!Head out and install the latest version of QlikView, make sure you install the examples.Navigate to C:\Program Files\QlikView\Examples\Extensions and double click the “Extensions Examples.qar” file. This will install all of the extension examples.Open the QlikView “Extension Examples that you can find in C:\Program Files\QlikView\Examples\Documents\ In the Mapping tabs you will find examples on how to plot either dots/points, lines or polygons.Attached to this post you will also find a dataset and an app that contains all of the high speed cameras in Sweden with corresponding latitude and longitude points if you want to play around with the extension, make sure you install the extension first, on your own.Keep on Qliking!Keep in mindExtensions are generally built upon web technologies such as HTML and JavaScript and for QlikView to be able to render these objects on the screen you will need to run QlikView Desktop with WebView mode enabled or access the document through the AJAX-client over AccessPoint. The IE-plugin does not support extensions.DisclaimerThe QlikView Mapping Example Extension can be configured to use many different map tile sources. Each map tiles source has its own terms and conditions and the user assume all responsibility for the selection of a source for map tiles and for compliance with the terms and conditions of the selected source. Any and all liability associated with the selection of a tile source and the compliance with the terms and conditions of the selected source is hereby disclaimed. ...View More

“If you use equality as a condition when comparing floats, I will flunk you!” I can still hear the words of the Professor in my first programming class when studying for my engineering degree. The threat was very real – he meant it – and the reason was of course the fact that you cannot (always) represent decimal numbers in an exact binary form. For example, we would never dream of writing a condition If( x = 0.3333333 , … ) when we want to test if x equals a third. Never. Because we know that a third cannot be represented exactly as a decimal number. No matter how many threes we add to the number, it will still not be exact. But it is not uncommon that people make comparisons with an exact decimal number, similar to If( x = 0.01 , … ) thinking that it is a valid comparison, although it leads to exactly the same problem as the previous comparison! This becomes obvious if you look at the hexadecimal representation of 0.01: 0.01 (decimal) = 0.028F5C28F5C28F…. (hex) The sequence …28F5C… is repeated an infinite number of times, but since QlikView uses a finite number of binary digits (all according to the IEEE standard), QlikView will internally use a “rounded” number. So what are the consequences? Well, QlikView will sometimes deliver the “wrong” number as result. Examples: Ceil( 0.15, 0.01 ) will return 0.16Floor( 0.34, 0.01 ) will return 0.330.175*1000 = 175 will return FALSETime( Floor( Time#( '04:00:00' ),1/24/60/60 )) will return 03:59:59 What you see are not errors in QlikView. And they are not errors in IEEE 754. Rather, they represent errors in the expectation and usage of binary floating point numbers. Once you understand what binary floating point numbers really are, it makes perfect sense. It's simply that some values cannot be exactly represented as binary numbers, so you get rounding errors. There's no way around it. Should you want to investigate this yourself, I suggest you start with the following script that generates 100 numbers and their rounded counterparts. In five cases the Ceil() function rounds "incorrectly" and generates a "Diff" different from zero: Load Num(Rounded,'(HEX) 0.000000000000000','.',' ') as RoundedHEX, (Round(100*Rounded) - PartsPer100)/100 as Diff, *;Load Ceil(PartsPer100/100, 0.01) as Rounded, *;Load RecNo() as PartsPer100 Autogenerate 100 ; So, what should you do? First of all, you should realize that the rounding errors are small and usually insignificant. In most cases they will not affect the result of the analysis. Further, you could avoid rounding with Floor() and Ceil() to sub-integer fractions. Also, you could convert the numbers to integers, because the errors will only appear if the numbers can have sub-integer components. For instance, if you know that you always deal with dollars and cents, you could convert the numbers to (integer) cents: Round( 100*Amount ) as Cents Or if you know that you never deal with time units smaller than seconds: Round( 24*60*60*Time#( Time, 'hh:mm:ss' ) ) as Seconds And finally, you should never use equality as a condition when comparing floats. Use greater than or less than. My professor isn’t here to flunk you, but rest assured: In his absence, QlikView will do it for him. HIC ...View More

I often use some sort of mapping in the QlikView applications I create to manipulate the data. Mapping functions and statements provide developers with a way to replace or modify field values when the script is run. By simply adding a mapping table to the script, field values can be modified when the script is run using functions and statements such as the ApplyMap() function, the MapSubstring() function and the Map … using statement.Let’s take a look at how easy it is to use mapping in a QlikView application. Assume our raw data looks like this:You can see the country United States of America was entered in various ways. If I wanted to modify the country values so that US was used to indicate the United States of America, I could add a mapping table like this to map all the variations of the United States of America to be US.Once I have a mapping table, I can start using it. I usually use the ApplyMap() function when I am mapping. The script below will map the Country field when this table is loaded.The results are a table like the one below where all the Country values are consistent, even the one that was misspelled (Country field for ID 4). The mapping handled all the variations that were entered in the data source and when the mapping value was not found the default ‘US’ was used.Now I could have also used the Map … using statement to handle the mapping. Personally, I have never used this statement but if you had many tables that loaded the Country field and you wanted to map each of them, Map … using provides an easier way of doing it with fewer changes to the script. After loading the mapping table, you can say: ... load data ...This will map the Country field using the CountryMap until it reached the Unmap statement or the end of the script. The main difference between this and the ApplyMap() function is with the Map … using statement, the map is applied when the field is stored to the internal table versus when the field is encountered.One last mapping function that is available in QlikView is the MapSubstring() function that allow you to map parts of a field. Using the mapping table below, the numeric data in the Code field is replace with the text value.Before MapSubstring() function is used:After MapSubstring() function is used:The numeric values in the Code field were replaced with the text values.Mapping is a powerful feature of QlikView that I use in just about every application. It allows me to “clean up” the data and format it in a consistent manner. I often use it to help scramble data when I have many values that I need to replace with dummy data. So the next time you are editing or “fixing” the data in your data source, consider mapping. Check out the technical brief I wrote on this topic.Thanks,Jennell ...View More

“Which products contribute to the first 80% of our turnover?”

...View More

The search functionality is central to QlikView. You enter a string, and QlikView immediately searches in the active list box and displays the matches. But what really defines a match? For example, should you find strings containing ‘Š’ when your search string contains an ‘S’? Or ‘Ä’ when you search for ‘A’?These may be odd questions for people with English as first language, but for the rest of us who use “strange” characters daily, these questions are important as the answers affect not just search results, but also sort orders.It is called Collation.A collation algorithm defines a process of how to compare two given character strings and decide if they match and also which string should come before the other. So, the collation affects everything from which search result you get in a query, to how the phone directory is sorted.Basically the collation is defined differently in different languages. Examples:The English collation considers A, Å and Ä to be variants of the same letter (matching in searches and sorted together), but the Swedish collation does the opposite: it considers them to be different letters.The English collation considers V and W to be different letters (not matching, and not sorted together), but the Swedish collation does the opposite: it considers them to be variants of the same letter.Most Slavic languages consider S and Š to be different letters, whereas most other languages consider them to be variants of the same letter.In German, Ö is considered to be a variant of O, but in Nordic and Turkish languages it is considered a separate letter.In most western languages I is the upper case version of i, but in Turkish languages, I is the upper case of dotless ı, and İ (dotted) is the upper case of dotted i.An example of how these differences affect sort orders and search results can be seen in the pictures below: The search string is the same in both cases, and should match all field values that have words beginning with ‘a’ or ‘v’. Note that sort orders as well as search results differ.Hence: A number of differences exist between languages that have special characters or characters with diacritic marks, e.g. Å, Ä Ö, Æ, Ø, Þ, Ś, Ł, Î, Č. Sometimes these characters are considered as separate letters, sometimes not. Some languages even have collation rules for letter combinations and for where in the word an accent is found. An overview can be found on Wikipedia.So, how does QlikView handle this?When QlikView is started, the collation information is fetched from the regional settings of the operating system. This information is then stored into the qvw file when the script is run.Usually you don’t need to think about this, but should you want to test it yourself, just change the regional settings in the control panel (the Formats tab – not the Location tab), restart QlikView, and run the script of your application.Bottom line – should you need to change the collation, you should do it on the computer where the script is run.HICFurther reading related to this topic:Text searchesThe Search StringThe Expression Search ...View More

There are some common misconceptions that surround the field of Visual Design. One of them is that visual design is just the act of making something look pretty or to make something pop. However, Visual Design is more than that.While making something looks aesthetically pleasing is one of the important parts of Visual Design, it goes beyond that to effectively communicate to people what the product or the application is all about. Visual Design if not done well cannot help the user to answer basic questions like – What is this? How do I use it? Why should I care?These are some crucial questions that a design needs to answer in order for it to be user friendly and seamless.For instance, Figure 1 below shows information about Silicon Yogurt stores in the United States of America and a comparison between other brands. The information below is visually appealing but the problem is that I don’t get the story.Figure 1In Figure 1, the first set of information tells me about the total number of Silicon Yogurt stores and across how many malls, then my eyes go down to the average number of yogurt stores per state which is a high level information then a comparison is made between other brands and Silicon brand which again is something that talks about the detailed statistics and then it gives me information about other brands. All this information is delivered in bits and pieces and it is hard for someone to understand the point of this material as it is not organized in a proper high-level to low-level sequence.Whereas in Figure 2 below, all elements of the visual design are the same but the information is organized is in a way that tells a story and the info graphic makes more sense.Figure 2The Title introduces the topic, the map and piece of information goes hand in hand by giving a high level summary of Yogurt stores per State. Once a high level summary is given, my eyes then go to the details and a comparison is made side by side which is useful to understand instantly?The difference between Figure 1 and 2 was only about content organization which made a huge difference in creating a story to the article, something which is an important part of visual design. This is just one part but there are a number of other things that need to be thought about along with the look and feel of something.Organizing information in an effective wayLooking at the design problem holistically so that everything works in conjunction with each otherBalancing the look and feel such that no element in the design pops up unless intentionalChoosing the right colors, fonts and other UI elementsThese are some considerations that need to be taken care of while working on the visual design of something. And then of course there is making everything look neat and pretty. ...View More

Hierarchies are very common in all database and business intelligence solutions. Often they are used for authorization purposes, i.e. the permissions to see data follows a hierarchy. One example is an organizational hierarchy. Each manager should obviously have the right to see everything pertaining to their own department, including all its sub-departments. But they should not necessarily have the right to see other departments. This means that different people will be allowed to see different parts of the organization. The authorization table may look like the following: In this case, Diane is allowed to see everything pertaining to the CEO and below; Steve is allowed to see the Product organization; and Debbie is allowed to see the Engineering organization only. Hence, this table needs to be matched against sub-trees in the above hierarchy. Often the hierarchy is stored in an Adjacent Nodes table, and if so, the above problem is easy to solve: Just load the Adjacent nodes table using a HierarchyBelongsTo and name the ancestor field Tree. See the blog post Unbalanced, n-level hierarchies how this is done. If you want to use Section Access, you need to load an upper case copy of Tree and call this new field PERMISSIONS. Finally, you need to load the authorization table. These two last steps can be done using the following script lines: (The TempTrees table is the table created by the HierarchyBelongsTo.) Trees: Load *, Upper(Tree) as PERMISSIONS Resident TempTrees; Drop Table TempTrees; Section Access; Authorization: Load ACCESS, NTNAME, Upper(Permissions) as PERMISSIONS From Organization; Section Application; When you have done this, you should have a data model that looks like the following: The red table is in Section Access and is invisible in a real application. Should you want to use the publisher for the reduction, you can reduce right away on the Tree field, without loading the Section Access. In either case, this solution will effectively limit the permissions to only the sub-tree as defined in the authorization table. But what if you have the hierarchy in a horizontal hierarchy? Then you cannot use the HierarchyBelongsTo. The solution is not very different from the above one. The only difference is that you need to create the bridging table manually, e.g. by using a loop: Let vHierarchyDefinition = 'Board level,Director level,Department,Unit'; Let vNumberOfLevels = Len(KeepChar(vHierarchyDefinition,',')) + 1 ; For vAncestorLevel = 1 to vNumberOfLevels Let vAncestor = Subfield(vHierarchyDefinition,',',vAncestorLevel); Trees: Load distinct Upper([$(vAncestor)]) as PERMISSIONS, DepartmentID Resident [Horizontal Hierarchy] Where Len([$(vAncestor)]) > 0; Next vAncestorLevel Having done this, you will have the following data model: Bottom line is that it is fairly straightforward to implement a hierarchical authorization scheme. See more about Hierarchies in the Tech Brief Hierarchies. HIC Further reading related to this topic: A Primer on Section Access Data Reduction Using Multiple Fields Tips and tricks for section access in Qlik Sense (2.0+) ...View More

A couple of weeks ago I wrote about the Magic of variables and showed how you can use these in advanced expressions. Today’s post will continue on this topic, but now with a focus on the dollar expansions.

...View More