QlikWorld

JOINS Joins in data model should be used wisely otherwise there may be possibility of things going disastrously wrong. However, it is advisable to keep minimum branches while designing schema. QlikView uses for types of Joins, theoretically it is same as SQL Joins, but there is slight difference in syntax. SQL uses INNER, LEFT, RIGHT and FULL whereas QlikView uses INNER, OUTER, LEFT, JOIN. It’s better to use proper Join wherever necessary. The easiest way to flatten the data model is to join two tables together into one. For a table, where there is a key field and a description, it is very easy to add the description into the main table using a LEFT join (refer to the following section for an example.) Data is joined using all the matching fields in the Load statement, so care must be taken to ensure that all fields needed are included in the Load statement and that any field that is not needed as a link is aliased. A further consideration is that if there are any duplica...

QlikView VMWare Configurations QlikView works really well with virtual environments, but might not deliver its full potential because the way QlikView core architecture works internally with hardware. And as we know it's more memory dependent. Virtual instance of QlikView may not perform well as oppose to standalone instance.  The main point is that Qlikview's sometimes poor performance on virtual environments does not inherit from poor Qlikview design, but merely from the fact that the general idea of platform virtualization works in the opposite way of Qlikview architecture.  Purpose of the virtualization is to share the over-committing resources between multiple guest systems to optimize hardware usage, which does not benefit Qlikview, which needs dedicated CPU and memory resources to deliver the user experience which Qlikview system enables.  Essentially virtualization also has a bridge, and subsequent overhead, between the real hardware and the ap...

QlikView Data Model When you load your data in to the QlikView application, a data model will be created based on the tables and columns you have in your script and also the names of the columns and any resident loads and joins you have previously defined.  You will of course be driven by the type and structure of your data sources. These sources and the underling data will have to be manipulated within the script to deliver the Data Model that best suites your data for both performance and usability.  Approaches The Star Schema  The standard layout and structure of data presentation is the Star Schema. QlikView is generally most efficient when working in this space.   Within a Star schema model, the event data (transactions) reside in a central “Fact Table” and the attributes of the event reside in separate “dimension tables”. The diagram below shows the basic layout…  This model works well in a simplistic, single event scenario....

Fetch data from Internet QlikView reads database tables, spreadsheets, XML files, and text files, but did you know that it can also take data from a web page? If you need some standard data from the Internet, there’s no need to create your own version. Just grab it from a web page! How about ISO Country Codes? Here’s an example. Open the script and click on Web files… below Data from Files on the right-hand side of the bottom section of the screen. This will open the File Wizard: Source dialogue. Enter the URL where the table of data resides: Then, click on Next; in this case, choose @2 under Tables: Click on Finish, and your script will look something similar to this: Now, you’ve got a great lookup table in about 30 seconds—and another few seconds to clean it up for your own purposes. One small caveat though: web pages can change address, content, and structure, so it’s worth putting in some validation around this if you think there could be any volat...

Circular Reference While building a data model, it can be very easy to create circular references as you add in all your tables. As QlikView cannot handle these in its selection engine, they must be avoided. However, should your data model have them, QlikView “loosely couples” some of the offending tables. This means that the loosely coupled tables will not be restricted in any selection. Sometimes you will get the following message. This indicates that QlikView has detected a circular reference and has loosely coupled some tables in the data model.  If you view your table structure (Ctrl+T), you will notice that there are some dashed lines connecting at least one table; this is where the circular reference has been detected and needs your attention.  In many cases, including the preceding one, circular references are caused by commonly named fields being linked together (with synthetic keys or without) when they shouldn’t be. Removing ...

Incremental loads All data sources grow over the period and when dealing with large volume of data, time it takes to reload data increases along with growth of data. Incremental load is a technique whereby data is read from source and stored on a local drive as a QVD file. Subsequent loads are first done from the QVD file, then only the new (or updated) data is read from the source and finally saved again as a replacement QVD file ready for the next time.   There are couple of techniques that can be used to accomplish an incremental load, and which of this to use depends on a couple of factors you need to establish. Firstly, establish where the data is coming from, is it a text file (Such as log file), a spreadsheet or a database table? Secondly, is data just added to the source or is it uploaded? Or are there records possibly deleted too? The simplest from is when reading a text file (Such as system log), where data is only added to the end. For this,...

Synthetic Key Basics When two or more tables shares more than one common field, QlikView join tables with all fields in common, and this creates an additional table with fields which share both tables. An example of a data model with synthetic keys. Qlikview automatically associates common fields, created a synthetic key or complex key and leading to a third table called $Syn 1 Table. This is the synthetic table which stores the combination of values for the two fields which, form the synthetic key. The synthetic keys in a data model can cause the application to have slow response time and sometimes even consume all available resources. Therefore, need to be avoided when possible.  How to eliminate synthetic keys? There are number of ways, we can avoid QlikView to form Synthetic Key. 1. Using QUALIFY This enforces QlikView to qualify all/selected fields. Qualify implements the check on full path of the field (e.g. Field is created as combination of T...