2013

As a QlikView developer I am often asked to load images into QlikView. In some instances the images are associated to other data fields and in other instances they are to be loaded in to the application to help convey a message. For example, let’s say that you need to bring in flags of countries that are to display when the corresponding country is selected and you need to display an icon that indicates whether sales for a country are above or below a predefined threshold. It sounds like an easy enough task; so how do you do it?

 

Well, QlikView offers the developer the Bundle function that can be added to the Load statement. Bundle Load statement allows the developer to load the image files directly into the QlikView application for portability.

 

The Bundle Process is a very simple scripting process. The syntax for the Bundle Load should look like this:

image1.png

 

The image file should contain two fields:

  • The first field is an identifier such as an index number, image name, a key field that associates the image back to other data files, etc.
  • The second field should contain the path to the image.

 

This file contains the CountryID so that I can associate the flags with the countries

image2.png

 

This file contains just an image name because I am just using these icons as a reference and they are not associated to any fields in other files.

image3.png

 

Once the images are loaded into QlikView we can now reference them using the INFO() function and a standard IF Statement:

image3a.png

 

 

I am using the INFO() function to display the flags so whenever a Country is Selected, QlikView will know to go grab the corresponding image based on the CountryID. If we needed to, we could do the same thing with the symbols by simply adding a list box for Image Name and selecting an image from there. For this example though, I am using a standard IF Statement to display the symbol for the country sales.

 

Here is what it looks like when the user selects a country. The Info() function displays the image associated with CountryID =1 and because Sales were above the threshold the green square is displayed.

image4.png

 

One note of caution, when images are loaded into a QlikView application using the Bundle Load statement, both the amount of RAM and the size of the application increase so consider both the size and the amount of image files before deciding to use the Bundle LOAD statement.

I wrote a technical brief that outlines these steps in more detail. You can access it here.

 

Happy Qliking!

Henric Cronström

Fiscal Year

Posted by Henric Cronström May 28, 2013

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

 

A fiscal year other than the calendar year implies a number of additional requirements in the QlikView app: The most obvious is that the year used in all charts and reports must correspond to the fiscal year which runs over a different set of dates than the calendar year.

 

Bar chart.png

 

Further, the notation sometimes changes: You probably want to display years as ‘2012/2013’ instead of just the year number.

 

Also, other fields, e.g. Month and Week must be assigned to the fiscal year as well as the calendar year.

 

Finally, the sort order of field values changes in some cases. E.g. you want to sort the months using the first financial month as the first month: [Apr..Mar] instead of [Jan..Dec]. (Compare with the month order in the graph and the list box.)

List boxes.png

 

There is a very simple way to achieve this in QlikView:  Just add the necessary fields in the master calendar and use these in all situations where you need a calendar field. There are many ways that this can be done, but my suggested solution is the following:

 

  1. Create a variable that contains the month number of the first month of the fiscal year. Assuming that April is the first month of your fiscal year, this variable should get the value ‘4’.
  2. Create numeric values of the necessary fields. Usually the number of the fiscal year is defined by its end, so (again using April as the first month) April 2013 belongs to the fiscal year ‘2014’.
  3. Create dual values of the necessary fields.

 

The script for creating fiscal year and fiscal month then becomes:


Set vFM = 4 ;                                                          // First month of fiscal year

Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year
         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month
          *;
Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year
         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month
          *;
Load Year(Date)                              as Year,           // Your standard master calendar
         Month(Date)                            as Month,
        …

 

Table.png

 

Other fields, like week, day, etc. can also be created in a similar way.

 

A comment on the field naming: In this script I use lowercase ‘f’ as prefix for the numeric values and uppercase ‘F’ for the dual values. In real life you may want to just have the dual fields (no numeric duplicates) and name these differently, e.g. just ‘Year’ and ‘Month’. If you do, you must also rename the original calendar year and calendar month accordingly.

 

The bottom line is anyway that you can solve this problem just by adding a couple of lines in you master calendar. No set analysis is needed. And no complex chart expressions are needed.


Simplicity.

 

HIC

When using QlikView, sometimes we are faced with challenges such as how do we transform an Excel spreadsheet that looks like the image below into a trial balance chart that allows us to see the monthly activity for each company and account.

 

Excel.png

 

Well using various functions and features of QlikView such as:

•          CrossTable Load of Excel spreadsheet

•          IsNull() function

•          RowNo() function

•          Peek() function

•          Date functions

•          Preceding load

 

We can create a trial balance sheet that looks like this:

 

Trial balance.png

 

Creating this chart involves loading the Excel spreadsheet into QlikView in a format that works best for us using a CrossTable load.  After sorting this data by Company Number, Account Number and Month Year, we are ready to create the Opening and Closing fields that we will need for the trial balance chart.  Using the RowNo() and Peek() functions, we are able to create the opening and closing balance fields for each account number on a monthly basis allowing the user to see what the activity was like during any given month.  Here is a snippet of what that script looks like:

 

Script.png

 

You can view the step by step details of how the trial balance chart was created in this technical brief.

 

Jennell

Already when we were selling QlikView 3, we had received a fairly advanced customer demand from pharmaceutical companies. We solved it. And here’s how we did it. But first some background:

 

In the pharmaceutical industry, the sales reps are not the ones that sell the products. Instead, they visit physicians and demonstrate one or several products. Days, weeks or months later, the physician prescribe the demonstrated medicine to a patient, and the actual sale takes place when the patient buys the drug at a pharmacy.

 

The demand on QlikView was to show pharmacy sales data, not only per physician, but also per physician visited once, twice, three times, etc. In other words, the physicians should be grouped by number of visits, and this number should be used as dimension in a QlikView chart. A nested aggregation.

 

For QlikView 4 we had a solution for this. Well, solution is perhaps not the right word… There was a kludgy, hidden feature with which you could use a count of a field as dimension. Internally it was called the Doctor Controls.

 

First you had to enable this feature in the list of hidden settings.

 

Easter Egg 04.png

 

Then you could create your chart: Count(Visit) per Physician. After that, you needed to enable the “Display Result Count” in the chart:

 

Properties Small.png

 

The left graph shows graph as-is – without the “Display Result Count” enabled. It shows the distinct count of visits per physician, just as the dimension and expression of the chart are defined.

 

Graphs.png

 

But, by turning on the “Display Result Count”, the chart transformed into the right graph: The displayed dimension was now the equivalent to an Aggr(Count(Visit),Physician) and the displayed expression was Count(Physician).

 

We solved a customer’s problem at the time, but this was not a good, long term solution. And it was nothing we wanted to support. Instead we wanted a general solution for nested aggregations. Six years later – after much thinking – Håkan (the Inventor) came up with the Aggr() function for QlikView 7. It is a general function for nested aggregations that made the Doctors’ Special redundant.

 

Easter Egg 11.png

 

But like a relic from the past, the Doctor Controls setting can still today be found in the QlikView 11 list of hidden settings. It doesn’t affect anything - I hope.

 

HIC

 

Further reading on the Qlik history:

A Historical Odyssey: Quality - Learning - Interaction - Knowledge

A Historical Odyssey: QlikView 1

Are you stuck with a complex data model? Do you need some tips to resolve the linkage of different fact tables? Does your model have data knots, circular references or synthetic keys?

 

If you have ever faced any of these situations you've probably heard about Link Tables and Concatenate as methods that can help you to put some light in your data model. It’s hard to find a general rule to determine when you should Concatenate rather than joining tables by a Link Table so today we will highlight the main differences of both methods by reviewing usage case scenarios.

 

Lets start with some basic examples:

 

Concatenate

 

“This statement forces concatenation with an existing named table or the latest previously created Logical Table. A concatenation is in principle the same as the SQL UNION statement, but with two differences: first that Concatenate prefix can be used no matter if the tables have identical field names or not; and secondly that no removals of identical records are made”

Source: QlikView help

 

 

Most basic example of Concatenate usage could be when you need to merge two or more tables that have identical structures, let say you have a historical data warehouse with sales from 2005 to 2012 and then another table with 2013 sales live in you transactional database. To create a visualization of sales trends over the years you will want to have everything normalized in one fact table, Sales table, with the data from 2005 to 2013.

 

concatenate.png

 

Link Table(s)

 

“(…) a junction table is a database table that contains common fields from two or more other database tables within the same database. It is on the many side of a one-to-many relationship with each of the other tables. Junction tables are known under many names, among them cross-reference table, bridge table, join table, map table, intersection table, linking table, many-to-many resolver, Link Table, pairing table, pivot table, transition table, or association table. (…).”

 

Source: Wikipedia

 

This definition is mostly about relational SQL databases so if we adapt it for QlikView it could be something like the following.

 

Link Table: It´s a table that contains common fields from two or more tables (within the same database or not). Easy, right?

 

The most common scenario for using Link Tables will be to replace synthetic keys and to avoid circular references by joining two or more fact tables against a common set of dimensions.

 

linktable.png

 

See much more in the attached files.

 

AMZ

Enjoy Qliking!

Last month QlikTech released QlikView Expressor 3.9 which introduced some key features that not only strengthen data governance practices within a QlikView deployment, but also enable developers to extend QlikView Expressor functionality.

Enhancements include:

  • Conformance Mapping - enables consistent naming of attributes representing a given data element. It reduces the proliferation of unnecessary variations that can cause inconsistent results in data processing.  Conformance Mapping makes it simple to unify the attributes mapped to fields in a Schema.

 

  • Metadata Analysis Search and Usage -  helps locate QlikView Expressor artifacts (dataflows, schemas, types, connections, attributes, fields, etc.) within workspaces, projects and libraries that are used to manage and prepare data for QlikView and other data targets.

 

  • The Extension SDK - enables a set of toolkits and wizards that provide a framework with common functions for developing Extensions for QlikView Expressor.


In this article I will briefly cover the QlikView Expressor Extension SDK and provide a supporting reference with examples that demonstrate how it can be used. (You can learn more about these features in this Technical Brief.)

The Extension SDK

Typically, software packages provide a method to extend their functionality using a development kit or API. This allows a developer to build in new capabilities that otherwise would not be available in the out-of-the-box software. The QlikView Expressor Extension SDK enables a set of toolkits and wizards that provide a framework with common functions for developing extensions for QlikView Expressor. With extensions, developers can provide clients with tools to integrate data from specialized sources and perform specialized transformations on data. An extension’s operators, connections and schema integrate into the QlikView Expressor Desktop interface and seamlessly work in conjunction with other standard, enterprise artifacts and operators.

The Extensions SDK provides a set of compilation, utility, and data type conversion functions using built-in Datascript Modules (DSM). DSMs are the standard QlikView Expressor scripting module for Datascript,  based on the Lua open-source scripting language. Extensions built on these common functions add functionality to QlikView Expressor Desktop to support custom data sources and specialized transformations.

Fig6.png

Figure 1 - The Extension Builder

 

What is a QlikView Expressor Extension?

A QlikView Expressor Extension is a plug-in (or add-on) that adds new Operators and Metadata artifacts to those already included in the core QlikView Expressor product. Extensions can provide read,write,transform and work-flow-like operations  that are not available with the standard operators and artifact types. Once developed they can be easily packaged and distributed with the QlikView Expressor Extensions Manager.

 

read_dir.png

Figure 2 - Custom Operators for reading files from a directory or FTP

 

The Extension SDK Tutorial

Want to give it a try? Check out this tutorial and sample project to learn how extensions are built using QlikView Expressor.

 

NOTE: Please make sure you have QlikView Expressor 3.9.1 installed.

Samples available in the project include:

  • Reading Fixed Width Files
  • Reading delimited files from a directory
  • Reading a data file via FTP
  • Encrypting and Decrypting Data with a custom transform

 

Regards,

Michael Tarallo
Senior Product Marketing Manager
QlikView and QlikView Expressor
@mtarallo

When we start working on a new project, the customer requirements form the guiding rails for us to begin the process of designing. As we start brainstorming and throwing out ideas, we begin to fill the empty spaces with uncertain experiments. We then start making tiny decision which we stitch together part by part in trying to complete the big picture.

 

Initially to get started with drawing an outline of the project, we naturally tend to ask ourselves the basic questions of why, what, where, how. As the project progresses, we come across unforeseen hurdles, feedback, opinions and try to work our way through it to achieve the big goal.  However, in the process of doing so, there are very strong chances of digressing from the main purpose of the project. In trying to put the small pieces together, and focusing on how to make it happen, we sometimes tend to lose the purpose of why we are doing what we are doing.  This is when the question “Why” always comes handy to validate our work.

 

A design can never be a solution if it doesn’t fulfill the underlying purpose of its existence. Asking the question “Why” each time we complete our little milestones, that we set for ourselves, can do wonders to the final solution.

 

In the book called “The Shape of Design”, Frank Chimero points out - “Our mistake was the same as that of the creative person who places too much focus on How to create her work, while ignoring Why she is creating it. Questions about How to do things improves craft and elevates form, but asking Why unearths a purpose and develops a point of view. We need to do more than hit the right note. The creative process, in essence, is an individual in dialogue with themselves and the work. Why is usually neglected, because How is more easily framed.”

 

The process of design is most successful when it is an iterative process rather than a linear one. Asking the question “Why” at every stage to validate your work can not only help in creating a strong argument for your work but also help in delivering a legitimate story. So, a reality check by asking the question “why” at every stage of the process can help in creating the solution that one set out to provide in the first place.

For QlikView 7 we developed a number of features that would enable developers to make even more advanced applications. One such feature was the Buffer prefix – a prefix that you could put in front of a Load or a SELECT that would store the data on the local machine and automatically use the local data when appropriate. With it, you could e.g. load data from a slow ODBC connection just once a day and for other script runs use the buffer.

 

The buffered data needed to be compact and something that QlikView could load fast. So, the QVD file was invented to solve this need. Today, hardly anyone uses the Buffer prefix, but QVD files created with the Store command are often used.

 

QV07 Opening screen.png

 

Another new feature was the Aggr() function. Already for QlikView 4 we had a solution for having an aggregation as dimension. Well, solution is perhaps not the right word… There was a kludgy, hidden feature with which you could use a count of a field as dimension. Internally it was called the Doctors’ Special. We solved a customer’s problem at the time, but we were not satisfied: we instead wanted a general, good solution for nested aggregations.

 

Six years later – after much thinking – Håkan (the Inventor) came up with the Aggr() function. It is a general function that can be used both as dimension and as measure. It can be nested and it can internally use any aggregation. In other words: it is a general function for nested aggregations. A by-product was the calculated dimension, which was necessary in order to use the Aggr() as dimension.

 

The Intel Itanium processor was first released in 2001, but its sales had still not picked up when QlikView 7 was released four years later in 2005. Instead, a new kid appeared on the block: The AMD X64 architecture. The X64 turned out to be both cheaper and faster than the Itanium, and as a consequence it sold better.

 

We already had an Itanium edition of QlikView and we realized that we also needed an X64 edition. Porting the code was straightforward and QlikView 7 was now shipped in three editions: X86, IA64 and X64.

 

QV07 Icon large.pngQlikView 7 also brought the calendar object, the block chart, the box plot, the expression overview, the variable overview, the alerts and the reports.

 

As a curiosity, I can also mention that QlikView 7 was the first version with scroll bars for the sheet. None of the previous versions had had this. We had always had the opinion that scroll bars would steal screen space and not add any relevant functionality. Instead we had a zoom function so that you could fit the work area to the screen. But - it’s never too late to change one’s mind…

 

HIC

 

Further reading on the Qlik history:

A Historical Odyssey: QlikView 6 and Multi-threading

A Historical Odyssey: QlikView 8, Ajax and Set Analysis

A Historical Odyssey: The Doctors’ Special

Henric Cronström

Finding NULL

Posted by Henric Cronström May 2, 2013

NULL is not a value. It is a lack of value. It is a placeholder that marks nothingness.

 

So how do you search for NULLs? How do you find the customers that didn't buy product X? Or, how do you find the users that didn't log on this month? There is no search string that matches NULL and even if there were, you can’t select NULL.

 

NULLs cannot be selected explicitly, so to find the records with NULLs, the selection must always be made in another field. In the example of customers not having bought product X, it means that the Product field for some customers is NULL. Hence, you need to select the customers for which the Product is NULL.

 

In other words – you need to make the selection in a field other than where you have the NULL. And here’s how you do it:

  1. Set your selection criteria the normal way.
  2. Use Select Excluded on the field where you want to negate the selection

 

For example, if you want to find customers that have not bought Basket Shoes, then you should first select Basket Shoes from the Product list box. Then you will in your Customer list box have the customers that indeed bought Basket Shoes. But the grey customers are the ones you are looking for. So, right click, and Select Excluded. Voilà!

 

Customers.png

 

The second example was how to find users that have not logged this month. Analogously, you first select the month and then you negate the selection by using Select Excluded on the User list box.

 

A third example could be that you want to find the customers that have not bought any product at all. Then you should first right-click the products and Select All. This will maybe not change very much, but it will exclude the customers that never placed any orders. In other words: These are now gray and can be selected using Select Excluded.

 

A final example could be that you have a combination of criteria, e.g. you want to find customers that have not bought any shoes in the last few months. The method is still the same: Select relevant products and select relevant time range. The possible customers are the ones that have bought of the products in the time range, and the excluded customers are the interesting ones. Select Excluded!

 

Shoes.png

 

However, when you have a combination of selections, QlikView doesn’t always remove both of the initial selections when you select the excluded values, so to get it right you should combine it with a Clear Other Fields. A good, user-friendly solution is to put both commands in a button that you label Select Excluded Customers.

 

Button.png

 

If you want to read more about how to manage NULLs in your QlikView application, you should read this Technical Brief.

 

HIC

Filter Blog

By date:
By tag: