Regularly in a QlikView application, you need to load from a directory or set of identical files which simply have different file names but contain the same type of data. For example, log files generated by systems contain the same type of data in a set structure, but the filenames often reflect the time and date or the system the log file was generated from.In this blog, I will show you how to (really simply) load in to QlikView, a set of files that are identical in makeup but are not named the same.So, imagine we have a directory full of log files on your server as below...I need to load all of these log files in to my application, but I do not want a single load statement in my script for each of the 100+ files. So, firstly we need to perform a standard load from our table (.log) files. When you have done this using the qlikview script editor, you will have a simple load statement...LOAD Sequence#, Timestamp, Level, Hostname, Logger, Thread, Id, User, Exe_TypeFROM[vm-qvs12__Application_QVS_2012-12-04T13.25.38Z.log](txt, codepage is 1252, embedded labels, delimiter is '\t', msq);So this statement would load all of the columns listed from the specific file in the FROM statement. However, we want to utilise one single LOAD statement for all of our log files. By simply changing the filename to contain an asterisk, provided all of the columns that you are placing in your load statement exist in all of your files, using an asterisk will cycle through all of the .log files that start with the text "vm-qvs12__Application_QVS_" in the directory, and load the data in to your application.example:FROM[files\Log\QlikViewServer\vm-qvs12__Application_QVS_*.log](txt, codepage is 1252, embedded labels, delimiter is '\t', msq);Now we have all of the data in our application, we may want to know from which log file the data in the application came from. So, we can use some functions to give us some extra data.We can use the Filebasename() function to give us a column in the data that contains the filename of the loaded log file.LOAD filebasename() as SourceFilename, Sequence#, Timestamp, Level, Hostname, Logger, Thread, Id, User, Exe_TypeFROM[files\Log\QlikViewServer\vm-qvs12__Application_QVS_*.log](txt, codepage is 1252, embedded labels, delimiter is '\t', msq);Now, that was a simple example of the wildcard load. What if I wanted to load data from multiple files and enhance the script with something like a preceding load? To do this I would have to utilise a simple function/variable and a small loop. In the example below, you can see that I am loading from multiple xlsx files, again using the filebasename function, but I am also creating a column called Total in my preceding load. The For Each and Next keywords tell QlikView to loop through the files in the location that conform to the wildcard in the Filelist function.For each vFile in FileList('C:\Users\aby\Desktop\*wildcard.xlsx')Load Col2+Col3 as Total, *;Load *, Filebasename() as Sourcefrom [$(vFile)](ooxml, embedded labels, table is Sheet1);Next vFileHopefully I have managed to simplify some of the scripts you use today!ABY ...View More

Although QlikView 5 was a stable, good version with lots of functionality, it wasn’t good enough for the new hardware that came along. We had already seen the arrival of the 64-bit Itanium processor and we had responded by developing an Itanium edition. So far, so good. But when we experimented with large data amounts, we realized that although QlikView now could load and address very large amounts of data, QlikView couldn’t process the data fast enough. QlikView needed more processor power. The amount of memory was no longer the limiting factor – instead it was the processor power. About this time, Intel had just launched its first dual core processors. There were already multi-processor motherboards, but the dual core processors were cheaper and could in addition share cache. The introduction of the dual core processors and QlikView’s need for more processor power forced us to look at how we better could utilize parallel processing. Hence: Time for a re-write! Multi-threading – here we come. Already in the QlikView 5 server, there was some basic multi-threading: Each session was evaluated in its own thread. But now, we needed to take multi-threading several steps further; we needed to make the evaluation of every click multi-threaded. Multi-threading is not easy. The software process needs to have mechanisms that can determine that one thread should be split into several, and other mechanisms that merge several threads into one. The threads are asynchronous and sometimes a thread needs to stop and wait for the result of another thread. This means that you can get situations where two threads wait for each other. So you also need mechanisms to avoid such deadlocks. Anyway, we re-wrote large parts of the code and when we released QlikView 6 in the spring of 2003, it was multi-threaded. In the solution, the logical inference for a single click is multi threaded and the evaluation of several threads are merged together to form the final result. After the logical inference, the sheet objects need to be calculated. Each object is then a thread of its own that subscribe to all changes in the data model. “Subscribe” means that it is re-evaluated every time there is a change in the selection state. In addition, the chart engine is multi threaded, so that if an aggregation is made over a table with a large number of records, different parts of the table are aggregated in different threads. QlikView 6 also brought the LEF files, which enabled Client Access Licenses (CALs) for the server. It also brought the qvp protocol, the plug-in client, the OCX, the table viewer, copy and paste objects, the layout themes, semi transparency (GDI+), the color functions, variables in the layout, the grid chart, the gauge chart, the radar chart, the slider object and the language dll:s. We also introduced different license levels for the stand-alone version of QlikView 6: Enterprise, Professional and Analyzer, so that a customer could have different capabilities for developers, power users and standard users. QlikView 6 should be remembered for bringing the multi-threading that we use still today. This is the core of the modern QlikView that can use the power of today's multi-core hardware. HIC Further reading on the Qlik history: A Historical Odyssey: QlikView 5 and the Rainbow Border A Historical Odyssey: QlikView 7, QVD files and the Aggr() function ...View More

Calendar fields and time fields can be either cyclic or sequential. The sequential fields have values that are consecutive, never repeat themselves and have an open range. Examples of sequential fields are Year [.., 2011, 2012, ..], YearMonth [.., 2011-Dec, 2012-Jan, ..] and Date [.., 2011-12-31, 2012-01-01, ..]. This is in contrast to the cyclic fields that have a limited number of values in a closed range, where the values are repeated after a time. Examples of cyclic fields are Month [Jan..Dec], WeekDay [Mon..Sun] and WeekNumber [1..53]. This may seem obvious. Why do I write a blog post about this? Because I think that we often are caught in the conventional. As QlikView developers, we tend to use the existing functions as they are, without questioning what we really want to show. However, we can create any fields we want. It’s all about how data is best visualized and how the user can best interact with data. Your role as a QlikView developer is to create a user interface that supports a good user experience and enables the user. How the field values are constructed is a very important part of the user experience. This post is about encouraging you to use your imagination to create customized calendar fields. First of all, the same field can be created in two versions: as a cyclic field and as a sequential field. Think of the difference between Month and YearMonth, for example. The first contains cyclic months, the second contains sequential months: Month(Date) as Month // Cyclic MonthName(Date) as YearMonth // Sequential Date(MonthStart(Date), 'YYYY-MM') as YearMonth_Alt2// Sequential The above is true for most calendar and time fields: they can be created in pairs – one cyclic and one sequential. For example Quarters: Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) as Quarter // Cyclic QuarterName(Date) as YearQuarter // Sequential ... or Week numbers: Week(Date) as WeekNumber // Cyclic Dual(WeekYear(Date) & '-W' & Week(Date), WeekStart(Date)) as YearWeek // Sequential ... or Hours: Hour(Date) as Hour // Cyclic Timestamp(Round(Date,1/24), 'MMM DD, hh:mm') as DateHour // Sequential Secondly, you can use the Dual function to create fields that are cyclic and sequential at the same time, i.e. they have a cyclic textual (display) value, but an underlying sequential numeric value. This means that the same cyclic value, e.g. ‘Q1’, will be shown several times in the sequence. Dual(Month(Date), MonthStart(Date)) as SeqMonth Dual('W' & Week(Date), WeekStart(Date)) as SeqWeek Dual('Q' & Ceil(Month(Date)/3), QuarterStart(Date)) as SeqQuarter Such fields are very useful in charts. The chart below uses such a field (SeqQuarter) as first dimension and Year as second dimension. This way the color coding and the Year legend are created automatically. Recommendations: Create many fields in your master calendar. A few extra fields in the calendar table don’t cost very much – neither in terms of script execution time, nor in terms of memory usage. Create both cyclic and sequential fields in your master calendar Use cyclic fields in list boxes If you have a chart with a single dimension, a sequential field is often preferable If you have a chart with several dimensions, cyclic fields are often preferable HIC Further reading related to this topic: The Master Calendar Relative Calendar Fields Fiscal Year ...View More

Do you use the generic search engine on your OS to find the document you are looking for? Have you had any luck finding it within one minute? Well, I haven’t. So I decided to go back to the old fashion way. QlikView way. The associative way. Here is my background. I use Windows, so I search for something like “How QlikTech uses QlikView” in “Search programs and files” box above the “start” button. This is where the disaster begins. It found 1,270 items. I know I didn’t make that many documents with that name. What it’s finding is all the files that contain at least one of the key words in the file name, including as an attachment to emails. It also searches for the content of the document. It’s overwhelming to find what I am looking for from this long list, but Windows doesn’t give me an option to filter by a document type or by a folder these ppt may belong to. I just needed a way to simply find my files in my folder by the name and not the content . So I created this application called Find My Files.qvw. If you’d like to try it, download the app and specify the root directory in the box and hit “Reload” button in QlikView. If you start with the highest hierarchy directory, it may take longer than expected depending on the number of files you have. For example, I have 106GB used in my C: drive, and I care about only what’s in my user folder. So I loaded all the files in that folder, which is 37GB in total. It took only 1 minute to read in all the files. If you don’t want to read all the files, you can specify the extensions in the loop such as qvw, xls, xlsx, doc, docx, ppt, pptx etc. The reload time will be faster, especially if you are reading the entire hard disk.The script of this app is pretty simple, and it is one of the help sample codes. Many people may be already familiar with it. Using this app, I know there are 18 files in my machine for “Making a good design great” ppt and not 1270.Yes, there are various desktop search software available out there, but why is this app any better? This app also helps you organize your hard drive. For example, when you are running out of disk space and need to know which folder is consuming the most disk space, you may want to know which folder I can consider eliminating. Windows Explorer doesn’t give you the folder size, so you need to right click and see the properties to see how large the folder is. If the folder is 10GB, you sit and wait for the properties box to appear. Bummer. Use this application, and you will be more efficient with your work life. Download this application and the technical brief from here.Ho Ho Ho… Happy Holidays to you (belated)!Shimanta (Shima-Santa) ...View More

The holiday season is often a time when we consider our blessings, our shortcomings, our needs, and the needs of others. As a special Christmas blog post, I felt it may be good to share my experiences working with Tiny Hands International (THI), a non-profit organization that works specifically with orphans, street children, and sex-trafficking victims. It was an eye-opening experience for me that gave me insight into the needs of the victims as well as the organization itself.I was having lunch several months ago with the founder of THI, John Molineux, who is a good friend of mine from college. At one point in our conversation, John expressed that they had been looking for a good way to analyze the data they collect when they intercept trafficking victims at border checkpoints in Nepal. Each year, 10-15,000 women are deceived and trafficked out of Nepal where they are then sold as sex slaves. More specifically, John mentioned to me that they had been hoping for a tool that would allow them to visualize the paths the victims take when they are trafficked. This could be useful in identifying the most commonly used routes, and it could perhaps give insight into how they could best focus their efforts. Of course, I knew QlikView could be a great help to them, and we got to work loading THI’s data. Once they identified the paths of the victims geographically in the data, it was easy to use a map extension to visualize the paths, drawing the more highly traveled paths with thicker lines:The data is very raw, and THI is thinking of ways to optimize their collection and identification of paths, but even so, some trends and useful information can be seen with the map. For example, the dark red area on the east side of the map shows that a lot of activity is occurring at this checkpoint, including some of the thicker lines. Of course, using QlikView’s associative filtering, we can choose to display only the top 15 routes:Or focus in on a specific border checkpoint like the eastern one I mentioned earlier:We were also able to use an expression to color the most traveled routes in red, while coloring the less traveled routes in yellow and green:Overall, we were very excited to see start seeing the story the data was telling us, and ideas were flowing on how they could further optimize their data collection and leverage QlikView’s analysis in their planning.What I realized in working with Tiny Hands International is that charity organizations are businesses too. They also need the ability to analyze data and be as efficient as possible. Knowledge is power after all, and wasted time, money, and resources mean a less effective fight against injustice. More than that, working with THI was a staunch reminder for me that there are people in the world right now doing selfless honorable work to improve the plight of their fellow humans. Oftentimes I sit at my warm desk feeling separated from this world, but I hope that as I'm humbled through experiences like this, I will consider the ways I might help to affect positive change in the world.If you’d like to support the work of Tiny Hands International, please go to their website. A small gift from us could have a large impact on those in need. ...View More

Numbers and dates are a never-ending source of concern when building any BI solution.But in QlikView, there is always a way to solve a problem… First, QlikView can interpret and format numbers and dates using functions in the script:Alternatively, it can format them in the user interface, where each object, dimension or expression potentially can have its own number formatting:The common denominator for interpretation and formatting in QlikView is the Format Code. QlikView uses it as a parameter in many functions, and you can find it in the Format Pattern on the Number tab in the properties dialog.Some rules of thumb around the format codes:Unrecognized characters in the format code can prevent QlikView from interpreting a number.The codes are case sensitive. For instance, M means month, whereas m means minutes. Some symbols can be written in either upper or lower case, e.g. AM/PM (or am/pm) and XIV (or xiv). For these, the format code must correspond to what you want: TT for AM/PM; tt for am/pm; (ROM) for XIV and (rom) for xiv.With the format code you can force a rounded display, e.g., by specifying two decimals on a multi-decimal number or by specifying year and month, but not day, when displaying a date. Such a rounding will only change the display and not the underlying number. To change the numeric value, you need to use a rounding function, e.g., Round(), Ceil() or MonthStart().You can specify Integers to have leading zeros. You can also specify them as binary, octal, decimal and hexadecimal numbers. In fact, you can use any radix from 2 to 36. You can also format them as Roman numerals. Num( Number, '000000' ) as Number // Always with at least 6 digits. Leading zeros! Num( Number, '(R36)' ) as Number // Displayed in Radix 36 (like hex, but with 36 symbols) Num( Number, '(ROM)' ) as Number // Upper case Roman numeralsSee picture below for the different cases. Each column denotes a specific format code. The rows are examples of input numbers and how QlikView will format them.Float numbers are similar. If you need QlikView to interpret a number that has a decimal symbol different from the one in the environment variables, be sure to use the third and fourth parameters of the Num#() function to specify decimal character and thousand separator. A correct format code is not enough. Num#( Number, '0,0', ',' , '.' ) as Num // Number with decimal comma and point as separatorQlikView interprets the integer part of a date serial number as a date – the number of days from Dec 30, 1899. Date formats are different from country to country so you sometimes need to specify which format you want. Note that you can specify weekday also.. Date( MonthStart( date ), 'YYYY MMM' ) as YearMonthQlikView interprets the fractional part of a date serial number as time of day. This can be specified in hours and minutes, etc. Note that the TT symbol denotes AM/PM. If this is not used, QlikView will assume 24-hour notation. Time( Ceil( Time, 1/24/4 ), 'hh:mm' ) as Time // Time rounded downwards to nearest 15 minI recommend that you use interpretation, rounding, and formatting functions in the script to transform data into a form that you want.HIC‌Further reading related to this topic:Data Types in QlikViewGet the Dates RightQlikView Date fields ...View More

Yesterday we announced QlikView 11.2. The main new feature in this release is QlikView Direct Discovery. With this exciting feature, QlikView does not require loading all data into the QlikView in-memory engine anymore. QlikView Direct Discovery is a hybrid approach that leverages both in-memory data and data that is dynamically queried from an external source.Some of the main benefits of QlikView Direct Discovery are:Consolidate relevant data from multiple sources, including Big Data repositoriesIn addition to the current decision support environments like EDW, data marts and operational data stores, organizations are now also considering the big data storage environments, such as Hadoop, BigQuery or Teradata etc. The challenge is enabling business users to analyze data from all of these data sources in the same analytics application. QlikView Direct Discovery offers a hybrid approach solving this challenge. It allows users to seamlessly run queries on the big data stores while they do discoveries on the data extracted from their ERP systems, data marts, EDW or even from their excel files into memory. Maintain associations among all the data, regardless of where it is locatedThe level of details that is stored in the big data stores is usually very granular. Business users need an easier navigation means in this bulk of data. QlikView Direct Discovery leverages associations in the data, making extremely large datasets manageable. For example, a policy analyst who uses a QlikView app to analyze regional loss and revenue information on a daily basis, would know the region names, but he would not have any clues on the specific policy numbers for these regions. With Direct Discovery, he can select the regional info, as he would do every day, and QlikView would automatically associate and query the billions of policy-level information on the big data store and display the query results with the in-memory metrics on the same QlikView app for the selected region.Access Big Data without complex data modeling or programmingIt is very easy to use Direct Discovery feature on a QlikView application. The only thing that is changed is to use “DIRECT SELECT” on the load script instead of using “SQL SELECT”. This new keyword would indicate QlikView that the data source is a direct discovery source. In this case, QlikView would only load the field names and will bring the data to the user interface when a direct discovery field is used on a QlikView chart. More information on the technical aspect of this feature can be found in this technical addendum paper. A how-to video is also available here.Please note that QlikView 11.2 is a new release of QlikView, with one new feature, QlikView Direct Discovery. Please read the QlikView 11.2 Release Note document that is available on the download site before upgrading to QlikView 11.2 to learn more about the release and some of the bug fixes with this release. With this release, the QlikView 11 branch will be stopped and new service releases will be made for QlikView 11.2 instead. For more information on this subject, please contact the QlikTech Support team.Since we announced this new feature in October, we have seen great excitement in our customer and partner community as they see the potential of QlikView 11.2 enabling Business Discovery with Big Data, without any data size limitations. QlikView 11.2 is now available on our download site if you also would like to expand your business discovery capabilities on the data sets that were previously used separately, or not used, because of their bulk and the development effort required! ...View More

In 1973 English statistician Francis Anscombe published the paper Graphs in Statistical Analysis to stress the importance of data visualization. He wanted to show that graphs are essential to good statistical analysis. Commonly referred to as Anscombe's Quartet, he created four data sets of x & y coordinates whose statistical properties of mean, variance, etc. are nearly identical. He then graphed these data sets as four scatter plot charts and demonstrated just how different the data sets really were. These charts are an integral part of comprehending the data because we can instantaneously see the differences between the scatter plots, a task that would take much longer if we were to rely solely on the data table. The chart and the table are working together to give us the complete story. Anscombe's point was that the chart isn't subordinate to the data table. Both the data table and the chart help the reader understand the data but in different ways.Good data visualization works because our brains are hardwired to rapidly process visual stimuli using very little conscious effort. Even before actively reading the charts your brain has pre-attentively processed the information to spot the patterns, the similarities and the differences.So when planning your application consider tables AND charts rather than tables OR charts. They are two ways to learn the complete story in the data. ...View More

QlikView does not have any data types. Instead there is the dual format.But it is still relevant to talk about data types, because QlikView functions and operators always return specific data types, albeit in the dual form. Further, QlikView interprets dual parameters and operands differently depending on the expected data type.And how does this work for Boolean functions?All Boolean functions and operations, e.g. IsNull(), True() and comparisons such as Date=Today() return 0 for FALSE and -1 for TRUE. Why minus one? Because it is equivalent to setting all the bits in the byte to 1, which is how TRUE is represented in most software systems (as opposed to FALSE, where all bits are set to 0).Further, in situations where QlikView expects a Boolean, e.g. in the first parameter of the if() function or in a Where-clause, QlikView will interpret 0 as FALSE, and all other numbers as TRUE.This means that a number of functions can be used as either Boolean or numeric functions, e.g., Index(), Match(), Substringcount(), and FieldIndex(). For instance, the Match function compares an expression with a list of values and returns the position of the match. But when the Match() function is used in a Boolean position, it will be interpreted as TRUE or FALSE.So when you use Match() in a where clause, you will have a condition that is very similar to the SQL “IN” operator. Further, with the WildMatch() function you can use wildcards, just like in the SQL “LIKE” operator: Load … Where Match(Char, 'A','B') or WildMatch(Name,'*son') SQL SELECT … WHERE Char IN ('A','B') OR Name LIKE '%son'But the real power of Booleans in QlikView becomes obvious when you define flags in the dimensional tables. For instance, you can easily define a field in the master calendar that tells you if the date belongs to this year: If(Year(Date)=Year(Today()), True(), False()) as IsThisYearThen you can use this flag in expressions showing the numbers for this year only: Sum(if(IsThisYear , Sales))Or the same expression with Set Analysis: Sum({$<IsThisYear={'-1'}>} Sales)Similarly, you can create flags for almost anything: IsLastYear, IsThisYearToDate, IsThisMonth, IsShipped, IsFirstClassCustomer, etc.One nice thing about flags is that they are stored in the dimensional tables, which are relatively small tables. Hence, the flags don’t use much memory space. Secondly, QlikView evaluates expressions with flags relatively fast. A third advantage is that it is an efficient way for a script developer to define concepts that may be fairly complex, in a way that the business user easily understands.Conclusions:Use the Match and Index functions as Boolean functions in Where-clauses and If-functions.Create flags in the dimensional tables and use these as Booleans in expressions.HICSee what other QlikView developers think about flags: iQlik, QlikView Addict and QlikView NotesFurther reading related to this topic:Data Types in QlikViewAutomatic Number Interpretation ...View More

It is not uncommon that users want specific products or customers to be displayed in specific colors. The most obvious way to do this is to change the colors in the chart properties. This is in fact quite easy if you use the copy and paste functions found when you right-click a color button. Just copy one button and paste on another, and you have moved the color. This way you can assign which color is to be used for the different values of the field. However, a prerequisite for this to work is that the order of the field values doesn’t change. A more robust way is to use color functions. Usually, you want to set the color of a bar, line or bubble and this is done by using the “Background Color” on the Expression tab: By the way – don’t use Visual cues. This feature is old and not very versatile. Use color functions as described here instead. In the picture above, both the product ID and the color are hard-coded in the expression. However, if you want to define colors for many products, the if-function will not be manageable. Then it is better to store this information in a table – either in the database or in an Excel sheet or as an inline statement in a scriptlet that is included in the script. Hence, Create your color definition table and store it in an appropriate place. The Red, Green and Blue columns hold the different color components and define the color uniquely. Load the color definitions into a mapping table: ProductColors: Mapping Load ProductID, Rgb(Red,Green,Blue) as ProductColor From ProductColors Use this mapping table when loading the products table, creating a new field for the product color: Applymap('ProductColors', ProductID , LightGray()) as ProductColorThe third parameter, here lightgray(), defines which color the unlisted products should get. If you instead use null(), the unlisted products will be multicolored according to the color settings in the chart properties. Finally, use this field as product color in the charts: This way it is easy to define which color specific products, customers, or other dimensions should get. Which colors to use? Oh, that is a completely different topic: Stephen Few has a number of good general recommendations. Adam Bellerby has some recommendations on how to avoid problems for color blind users. Shima Auzins suggests using colors as warning signals. HIC ...View More