Today’s corporate environment is global. QlikView application developers are required to support many regions and many languages. So how does a QlikView application developer handle multiple languages? Well, that depends on how the data is captured and the overall goal of the application developer. Let’s look at two scenarios.Scenario 1: The developer only needs to translate the labels and textboxes within the application and not the actual data. Create a translation spreadsheet that contains an index column to be used for reference and columns for every language that each label and text entry that needs to be translated. Fill in the English language column with the text to be translated and have translators fill out their language. In the script load the translation spreadsheet and create an inline table that contains all of the language translations. Then create a variable, say vDataLanguage, that reads the Language field. Using set analysis, create an expression that references the index number and the language to display =Only({<Index={12}>} [$(=vDataLanguage)]) for all textbox and object captions that need to be translated. Scenario 2: The data from which the QlikView application developer is developing is recorded in English but he/she needs to have some of the actual data translated into other languages. The first thing that he/she needs to do is determine the fields that need to be translated and create a translation spreadsheet that contains a worksheet for each of the fields to be translated. For example, we need to translate the Industry data. Now we need to load the data translation file into QlikView as a Mapping Load for each language. For this example we need to translate into German and French so we’ll create a mapping file for French_Industry and a mapping file for German_Industry.Create an inline table that contains all of the language translations. Create a variable that reads the Language field (vDataLanguage). In the script, find the Industry Field that we need to translate. Using the Applymap function we can translate the English data into the different languages and be sure to name the translated field as Fieldname_Language (e.g. Industry_French) . We now need to create a variable for each translated field. So for industry we’ll create vIndustry. The expression in vIndustry is =chr(91)&'Industry' &chr(95) & '$(vDataLanguage)' & chr(93) .Now to get the correct language to display in a list box you need a calculated dimension using this expression, which is =$(vIndustry).That’s it! Easy, right? Well, it is a bit time consuming if you are trying to translate data in fields but translating text and labels is really not that difficult. I created a technical overview that walks you through the two scenarios mentioned in this blog as well as a third scenario which can be found here. This resource includes a How-To, sample data files and 2 QVWs. Good luck with the translations! Lycka till med översättningarna! 翻訳で頑張ってください！ ...View More

Productwise, QlikView 5 was about consolidation. We did not change very much from QlikView 4. We already had a feature-rich product – now it was about improving the details and making it more stable. So we improved the functions in the UI – made it look nicer, introduced a layout menu and a layout undo, made it possible to copy and paste objects, introduced the current selections box, an input box, fast type change in charts, windows selection style and fuzzy search. And we made it possible to export data to Excel. We also improved the data loading capabilities. Now it became possible to load Unicode characters and HTML tables as well as XML files. It also became possible to load from SQL stored procedures. And the script editor was equipped with a debugger. We developed the server further and made it more stable, but we really didn’t change much in the basic architecture. None of the above was incredibly exciting or ground-breaking. Instead, QlikView 5 could perhaps be remembered for one less impressive feature: the rainbow border. This was probably one of the less useful features we have introduced. Whether it was beautiful, I leave to you to decide .... During the lifetime of QlikView 5, Intel introduced a new, promising processor architecture: the Itanium, a 64-bit RISC processor for PCs. This was the future! With such a processor QlikView would no longer be limited by the 3GB restriction on the amount of data it could handle. This opened up a fantastic new opportunities! QlikView would be able to load much larger data amounts than before! We immediately started to develop an Itanium edition of QlikView. As a consequence, the later releases of QlikView 5 were all compiled into both a 32 and a 64 bit edition. QlikView 5 therefore goes down in history as the first 64-bit version of the product (even though QlikView 6 was the first version where there was an official 64-bit version.) As we developed QlikView 5 during year 2000, QlikTech got new investors which led to a change of management. When we released QlikView 5 in the spring of 2001, Måns Hultman had been appointed CEO. Måns had a very clear picture of what the company needed: A refined sales strategy and a bigger sales force. The new sales strategy was simple: Focus. Rather than selling QlikView as a tool that could do anything, we would focus on financial applications on top of ERP systems, especially Movex (which was a common system in Sweden at that time). The target prospects were controllers and CFOs. And it worked. With Måns’ new strategy and a good, stable QlikView 5, the company started growing faster than ever before. The course was set for becoming a public company. HIC Further reading on the Qlik history: A Historical Odyssey: QlikView 4 and the first Server A Historical Odyssey: QlikView 6 and Multi-threading ...View More

Today I have a blog post for the Geeks¹. For the hard-core techies who love bits and bytes. The rest of you can stop reading now. For you, there are other interesting posts in the Business Discovery Blog and in this blog, the QlikView Design blog. Now to the bit-stuffed pointers: During the QlikView script run, after each load statement, the Qlik engine transforms the data loaded into two table types: one data table and several symbol tables. The engine creates one symbol table per field: The symbol tables contain one row per distinct value of the field. Each row contains a pointer and the value of the field, both the numeric value and the textual component. Basically, the symbol tables are look-up tables for the field values. The data tables are the same tables as you can see in the QlikView internal table viewer (<CTRL>-T) when you have chosen the “Internal table view” – the same number of rows, the same number of columns. However, the tables do not contain the data itself – they contain the pointers only. But since the pointers can be used to look up the real value in the symbol tables, no information has been lost. These pointers are no ordinary pointers. They are bit-stuffed indices, meaning – they only have as many bits that it takes to represent the field, never more. So if a field contains four distinct values, the index is only two bits long, because that is the number of bits it takes to represent four values. Hence, the data table becomes much smaller than it would have been otherwise. The bit-stuffed pointers and the symbol tables are the reasons why the Qlik engine can compress data the way it can. Understanding this will help you optimize your document. It’s obvious that the number of records and number of columns in a table will affect the amount of memory used, but there are also other factors: The length of the symbols will affect the size of the symbol table. The number of distinct values in a field will affect the number of rows in the symbol table as well as the length of the pointers. When creating QlikView scripts, always ask yourself if there is any way to reduce these numbers, to minimize the memory usage. Here are a couple of common cases: You have a long, concatenated, composite key that you don’t need to display. Use Autonumber() and the symbols will take no space in the symbol table. The integer values will instead be calculated implicitly. You have a field with many unique timestamps. Then you are sometimes better off if you first split it into two fields – Date and Time – and round the Time downwards to closest 15-seconds interval or to nearest full minute, e.g.: Date(Floor(Timestamp)) as Date, Time(Floor(Frac(Timestamp),1/24/60)) as Time,These expressions will give you at most 24*60=1440 distinct time values (11 bits) and typically 365 distinct dates (9 bits). In other words, as soon as you have a timestamp field with more than 1 million (20 bits) distinct values, the pointer for the timestamp field takes more space than the pointers for the two individual fields. And for the number of rows in the symbol table(s) you hit the break-even much sooner. So you should consider splitting it into two fields sooner, maybe when you have around 100k distinct values. If you found this post interesting, I greet you welcome to the QlikGeeks. HIC PS. All of the above is of course true for both QlikView and Qlik Sense. Both use the same engine. If you want to read more about QlikView internals, see also Logical Inference and Aggregations Colors, States and State vectors The Calculation Engine ¹ Geeks, see picture: ...View More

Back in 2001, with the release of the now cult-classic film Donnie Darko, there was a website that came out to accompany the film. The site was (and is) as cryptic and vague as the movie. There is this quasi linear path through the site but at the same time no clearly delineated objective - you experience the site and you do so just for the sake of experiencing it. It doesn't need to tell you what theaters the movie was being released in, or when it was being released because you could get that information other places. This site was for enthusiasts, for die-hard fans. It was something to play with that added to the richness of the film by extending the experience of the film.Now contrast this with a site that sells movie tickets. There is a clear objective. You use it to buy tickets online to save you from having to buy tickets in person. I go to these sites with a purpose, I complete my task, I leave. It's a tool to do a job.The balance between creating a tool vs. creating a toy isn't always this clear. You want to create a pleasant experience, even something cool, but if the primary objective is to enable people to complete a task then you should focus on building a useful tool. Help your users do the thing they came here to do. In the case of QlikView this means building applications that focus on the user's needs, apps that clearly show the data, that help users explore their data and find new insights. I've seen more than a few applications where people, with the best intentions, tried to create a fun environment but ended up adding too many pieces of unnecessary visual "flare." When considering a background, a chart, an animated logo, or a photograph you need to ask yourself: "is this going to help people use this application?" If the answer is no, then perhaps it is best left out. There are plenty of well designed applications that take a very minimalistic approach to design. The application should be well designed, but well designed this isn't synonymous with having a trendy aesthetic. You don't need to cram in all the design tricks you know. Real design solves problems and the application you are building should be a tool to help people solve problems and complete tasks. ...View More

There are no data types in QlikView. This is not a limitation – it is a conscious design decision. One of the initial requirements of QlikView was that it should be possible to mix data from different sources: We wanted users to be able to have a data model with some data from a database, some from an Excel sheet, and some from a comma delimited text file. Some of these sources have proper data types, others don’t. So relying on the data types of the data source would be difficult. Further, we wanted the internal functions to be able to always return a relevant calculation – there must never be any type of conversion problems. We wanted simplicity for the user. Enter the Dual format. The dual format is a brilliantly simple solution to the above requirements: Its core is that every field value has two values – one string that is displayed, and one number that is used for sorting and calculations. The two values are inseparable; they are like the two sides of a single coin. They are both needed to describe the field value properly. For example, months have the string values ‘Jan; ‘Feb’ … ‘Dec’, which are displayed. At the same time they have numeric values 1 to 12, which are used for sorting. Similarly, weekdays have the string values ‘Mon’; ‘Tue’ … ‘Sun’ and at the same time the numeric values 0 to 6. Dates and times have string values that look like dates, e.g. ‘12/31/2011’ or ‘06.06.1944 06:30’ and at the same time they have numeric values corresponding to the number of days since Dec 30 1899. As I write this, the (numeric) time is 41215.6971. How months, weekdays, dates and times should be displayed is defined in the environment variables in the beginning of the script. This way QlikView can sort months, days and dates numerically, or calculate the difference between two dates. Numbers can be formatted in arbitrary ways. In essence, QlikView can have data that is much more complex than plain strings. When dual values are used as parameters inside QlikView functions, the function always uses just one of the two representations. If a string is expected, as in the first parameter of Left(s,n), the string representation is used. And if a number is expected, as in the Month(date) function, the number representation is used. QlikView functions all return dual values, when possible. Even the color functions do, see table. However, string functions, e.g. Left(s,n), is the exception; because they don’t have a number that can be associated with the string, they leave the number part of the dual empty. Finally, there is of course a function with which you can create your own dual values: Dual(s,n). With it you can create any combination of string and number. Use it if you want to assign a sort order to strings or if you want to assign text to numeric values. Here is how one of the top contributors of QlikCommunity uses duals for Booleans: Rob Wunderlich’s Dual flags. HIC Further reading related to this topic: On Boolean Fields and Functions Automatic Number Interpretation ...View More

Yesterday we announced the beta version of QlikView 11.2. QlikView 11.2 is a new release of QlikView that will be available in December 2012. The main new capability of this release is QlikView Direct Discovery. We are very excited about this capability as it truly expands the potential use cases for Business Discovery, enabling business users to conduct associative analysis on big data.Today there are many QlikView customers that use QlikView to analyze terabytes of data. QlikView’s patented in-memory data engine compresses data by a factor of 10 allowing associative in-memory analysis on very large data sets. King.com is one of these customers analyzing billions of rows of online gaming data with QlikView on top of Hadoop system. Now with QlikView Direct Discovery, QlikView users can do Business Discovery on the external data sets without loading the data into QlikView in-memory data model. What’s really special here is that with this unique hybrid approach business users get the QlikView associative experience even with data that is not stored in memory. This is amazing!One thing I would like to emphasize is the real value of QlikView Direct Discovery is its hybrid approach. It allows users to seamlessly analyze data from multiple sources (with the in-memory data) within the same interface without any size limitations. The users can associatively make selections in any of the data sets (in-memory or Direct Discovery) and always see what is associated and not associated with the same meaningful QlikView colors: green, white, and gray.Another great advantage of QlikView Direct Discovery is the capability to query data for more up to date information in the use case scenarios where recency really matter.How does QlikView Direct Discovery work? QlikView determines which data resides in-memory and which data is direct discovery data by using special script syntax, “DIRECT SELECT”. This allows certain data elements dictated by the script syntax not to be loaded into the QlikView data model during the script reload process, but still be available for query purposes in QlikView objects in the user interface and to be combined for analysis with the QlikView in-memory dataset. The video provides a short introduction on how to set up direct discovery. I highly encourage you to read the technical addendum paper to understand the best practices and some of the limitations that exist with the initial release.I am personally very excited about this capability and cannot wait to get my hands dirty to try out different use case scenarios where the query results from big data sources can be leveraged with unique QlikView in-memory features! ...View More

Our current month names come from the Roman calendar. The original Roman calendar had ten months: Martius, Aprilis, Maius, Junius, Quintillis, Sextilis, September, October, November and December. The first four months were named after gods: Mars, Venus (Aphrodite in Greek and Apre in Etruscan), Maia and Juno. The origin of Aprilis is debated, but we know that the month was sacred to Venus because the Festum Veneris et Fortunae Virilis was held on its first day. The last six months are based on the latin words for five, six, seven, etc. Each year started in March and ended in December, 304 days later. It was then followed by a period of festival between the years. But the calendar was soon changed by the king Numa Pompilius around 700 BC, who added Januarius (after the god Janus) and Februarius (after the purification festival Februa). He also moved the beginning of the year to Januarius. However, the year was still too short - only 355 days - so the Pontifices occasionally had to add an extra month to keep the calendar in sync with the seasons. This decision was political and was sometimes done just to extend the term of a particular public official. Or it wasn’t done at all, if the official was an opponent. By the 1st century BC, the calendar had become hopelessly confused, so in 46 BC, Julius Caesar initiated a reform that resulted in the establishment of a new calendar, the Julian, which was a vast improvement: Leap years were introduced and the year in the Julian calendar was on the average 365.25 days, so no extra intercalary month was needed. After Julius’ death, the month of Quintilis was renamed Julius in his honor, hence July. Julius Caesar was succeeded by Augustus, and after his death the senate renamed Sextilis after him. At the same time, the senate also suggested that September be renamed after the reigning Caesar Tiberius. But Tiberius refused with the words: “And what will you do if there be thirteen Caesars?” Today most countries use the Gregorian calendar, which is based on the Julian and still has the Roman month names from 2000 years ago. In QlikView and in Qlik Sense, the abbreviated month names are defined in the environment variable MonthNames. Set MonthNames = 'Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'; This variable is used by several functions, e.g. Month() and Date(). The format code ‘MMM’ is always mapped against the content of this variable. You can also create an environment variable for the long month names, corresponding to the format code ‘MMMM’: Set LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December'; If you have these variables, you can use the long format codes not only for formatting of date fields, but also for interpretation of fields, e.g. Date(MonthStart(Date),'MMMM YYYY') as YearMonth, // Creates and formats YearMonth Date#(Date,'MMMM DD, YYYY') as Date, // Interprets the Date='October 9, 2012' as a date Dual(MonthTxt, Month(Date#(MonthTxt,'MMM'))) as Month, // Interprets the MonthTxt='Oct' as a month Bottom line: You should define fields for months in your master calendar and add an environment variable for long month names. HIC Further reading related to this topic: Ancient Gods and Modern Days The Date Function ...View More

Have you ever received a design and wonder how you were going to replicate it in QlikView? Well, I came across this challenge when creating the Social Media Data Analysis demo. Our UI designer designed a chart that that looked something like the image below: Now at first glance, you may consider designing this in QlikView using individual text objects for the labels and expressions. That was my first thought but then I realized that there would be a lot of text objects to maintain when changes were made to the expressions. So I tried to recreate this chart using a pivot table. While it was easy to add the dimension and the three expressions, it was a little more difficult to add the perks like a background color for the highest value but only for selected companies. For example, in the chart below, Company A, Company C and Company D are selected so I only want to see the highest values based on those selections.There were also challenges like the dimension label and background color or the fact that the background color for Company A Followers extended to the left to the label. It was little things like this that made this chart a challenge. But with the use of set analysis, background colors, text color and some carefully placed text objects, I was able to create a visualization that was not as pixel perfect as the Photoshop design but close to it using the basic features and functionality of QlikView. Read more about how I created this chart in this technical brief. ...View More

A common situation when modeling the data for a Qlik application is that you have several fact tables and the fact tables have mixed granularity. An example is budget vs. actual numbers, where the budget typically is made on a higher level than the actual numbers, e.g. the budget has regions instead of specific customers and months or quarters instead of specific dates. It could also be that you want to have different granularity in a mixture, e.g. you want full details for the numbers for the current year, but – due to the amounts of data – you want to compare these to aggregated numbers from previous years. In a Qlik data model, it is possible and not very difficult to use a fact table with mixed granularity. Say for instance that you have a detailed fact table with the numbers for current year: In addition, you have an aggregated fact table for previous years: Instead of CustomerID, ProductID and OrderDate, you have Country, CategoryID and OrderMonth as foreign keys. The solution is to concatenate these two tables into one common fact table and use generic keys for the three dimensions. The generic keys contain information about both the higher and lower levels of the dimensional hierarchy and can be used for either the higher level only or for both levels. This way, the detailed records of the fact table link to customer, product, and date, while the records with aggregated numbers link to country, but not to customer; to product category but not to individual products; and to order month but not to individual dates. It can sometimes be tricky to create the generic keys because the detailed fact table lacks direct information about the higher levels in the dimensional hierarchies, e.g. country and product category. But this can be solved using the function Applymap. For the detailed part of the fact table, the generic keys can be defined as: Applymap('MapCustomerToCountry',CustomerID) & '|' & CustomerID as %CustomerID, Applymap('MapProductToCategory',ProductID) & '|' & ProductID as %ProductID, 'Date:' & Num(OrderDate)) as %OrderDate And in the aggregated part of the fact table, the corresponding definitions could be: Country & '|' & Null() as %CustomerID, CategoryID & '|' & Null() as %ProductID, 'Month:' & Num(MonthStart(OrderMonth)) as %OrderDate The generic keys must be mapped to the real keys using a dimensional link table, but once this is done the application will work like a normal QlikView app. This method can be used in a number of cases where you want to define keys that link to several values simultaneously, the most common one being comparison of actual numbers with budget. Read more about generic keys in the Technical brief on Generic Keys. HIC ...View More

The use of the seven-day week is ancient. Signs are found in the old Greek, Indian, Persian, Babylonian, Jewish, Akkadian and Sumerian cultures. Most likely it was invented by the Sumerians around 4500 years ago. The Sumerians named the week days after the celestial bodies: The sun, the moon and the five known planets. Since the planets had names after gods, some days were thus also named after gods. These names were then translated into Babylonian, then into ancient Greek. In both translations the corresponding local gods were used. Set DayNames='Nanna;Gugalanna;Enki;Enlil;Inanna;Ninurta;Utu'; // Sumerian Set DayNames='Sin;Nergal;Nabû;Marduk;Ishtar;Ninurta;Shamash'; // Babylonian Set DayNames='Σελήνης;Ἄρεως;Ἑρμοῦ;Διός;Ἀφροδίτης;Κρόνου; Ἡλίου'; // Greek Set DayNames='Moon;Ares;Hermes;Zeus;Aphrodite;Cronos;Sun'; // Greek The Romans, however, did not initially use a seven-day week. But a decision by Emperor Constantine in AD 321 eventually established the seven-day week also for the Roman Empire. Once again the names of the gods were translated to their local counterparts: Set DayNames='Lunae;Martis;Mercurii;Iovis;Veneris;Saturni;Solis'; // Latin Hence, the Latin names of the gods were mostly translations of the Greek names, which in turn were translations of the Babylonian names, which go back to the Sumerians. The Latin names can still be recognized in most Romanic languages, e.g. in French: Set DayNames='Lundi;Mardi;Mercredi;Jeudi;Vendredi;Samedi;Dimanche'; // French The Germanic tribes in northern Europe started to use the seven-day week long before they converted to Christianity, so the day names, except sun day and moon day, have the names of the old Germanic gods: Tyr/Tiw, Odin/Wotan, Thor/Donar and Freyja/Frige. But for Saturday, the day was not translated. It is still “Saturn’s” day in e.g. both Dutch and English. And in all Nordic languages it is the “Washing day”. Because that is what you were supposed to do on Saturdays. In QlikView, you can customize the day names by changing the variable DayNames as I have done above. These values will then be used by the WeekDay() function. Alternatively, you can create a new variable, e.g. vDays, and use this in the following field definition: Dual(Subfield('$(vDays)',';',WeekDay(Date)+1),WeekDay(Date)) as WeekDay But on which weekday does the week start? And which week is the first week of the year? The ISO 8601 defines these things clearly: The week starts on a Monday Week no 1 is the first week of the year with four days or more. This means that if Jan 1st is a Friday, then week no 1 starts Monday Jan 4th, and the first three days of the year belong to the last week of previous year. It also means that if Jan 1st is a Thursday, week 1 starts Dec 29th. The ISO 8601 is used in many countries, among them most European ones. But in North America and in the Middle East, different conventions are used. Often Sunday is considered the first day of the week. And Jan 1st is in some countries always part of week 1. As a consequence, the first and last week of the year are often fractional. The QlikView week functions all use ISO 8601. If you want to define these entities in a different way, do the following: Introduce two variables that define your new week system: Set vFirstWeekdayOfWeek = 6 ; // 0=Mon, 1=Tue, 2=Wed, ... , 6=Sun. (For the U.S. = 6) Set vMinDaysInWeek = 1; // Minimal number of days in week no 1. (For the U.S. = 1) Then you can define your week fields as: Load *, Dual(WeekDay(Date),Mod(WeekDay(Date-$(vFirstWeekdayOfWeek)),7)) as WeekDay, Ceil((Date-FirstDateOfWeekOne+1)/7) as WeekNumber, Date(WeekStart(Date-$(vFirstWeekdayOfWeek))+$(vFirstWeekdayOfWeek)) as WeekStart; Load *, Date( WeekStart(YearStart(Date)+$(vMinDaysInWeek)-$(vFirstWeekdayOfWeek)-1) +$(vFirstWeekdayOfWeek)) as FirstDateOfWeekOne, … Bottom line: Define fields for week day and week number in your master calendar. And don't hesitate to change the environment variables if you want long day names or different day names. HIC Further reading related to this topic: Roman Emperors and the Month Names Redefining the Week Start ...View More