In Data modeling in QlikView Part 1 we learned some important topics in QlikView data modeling. In this blog also i am following the same objective, to give high level concepts on some of the important transformations in QlikView and encourage developers to explore more.
QlikView application consists of 2 parts – Visualizations and Data model. Your visualization will display the underlying data.Therefore creating a robust Data model is key to any successful application.
1. Concatenate in QlikView
In QlikView, if the number of fields and field names of two or more tables are same then the tables are Concatenated into one table. There are many flavors in Concatenate like Automatic Concatenate,Forced Concatenate and No Concatenate. Concatenate tables can be useful in resolving synthetic keys.
Concatenate and NoConcatenate in QlikView
Tip : Concatenate is very helpful when you have to include 2 or more Fact tables in your data model. These Fact tables should be of same granularity
2. Link Table in QlikView
Whenever you will hear about Concatenate, you will hear about Link table as well. Link tables are also used when you have to add multiple fact tables in your data model. These fact tables may be of different granularity and associated to same or different dimension tables.Link table is a means to resolve synthetic keys. Link table contains the common fields from multiple tables.
3. Partial Load
Partial load is used when you want to load just one table in your data model/script without reloading all the other tables. For example, you are in development phase and your script contain 10 tables but you want to just reload one table since data in table changed or the structure of this table has changed. If you perform normal load then all 10 tables will be loaded that may take time. But with Partial load you can just load one table.
To perform partial load you have to prefix your load statement with ADD or REPLACE keywords.
Prefixing your load statement with ADD will append the new records. Prefixing your load statement with REPLACE will drop the exisitng table and create a new table.
Tip: To perform Partial load, use File menu ->Partial load or Ctrl + Shift + R. Partial load functionality is useful in development. Remove ADD or REPLACE when your development is completed and you are migrating your application on the server.
4. Cross table in QlikView
Load statement is prefixed with CrossTable when you want to load a CrossTable. A CrossTable is a table structure with Orthogonal list of headers. If a CrossTable is loaded as a normal table then each of the column will be loaded separately as an individual dimension.Performing aggregation on such a table will be difficult. Also it will consume lot of memory. CrossTable prefix will transform columns into rows.
5. Section access
Security is an important part of an application. Section access is used to provide data level security to your application. By using Section access you can limit what data authorized users can see. Section access is defined in script editor as a hidden script. The access control data can be managed through Text files,databases or Inline tables.
6. Binary Load
When we talk about Section access , we talk about Binary Load as well. Binary load is used when you want to load the script part of another qvw file. You can have only one Binary load in a qvw and it has to be the very first statement in your script.You can only load one qvw in your document. It loads just the script part, visualization is never loaded. It is useful when applying security using Section Access. You can develop security guidelines in a separate QVW and then add that QVW using Binary Load in all the other documents.
7. Interval Match
Interval match function is used with Load statement for linking the discrete values to one or more numeric intervals.It can be used for example, in scenarios where you have 2 tables one table containing Shift intervals like Start and End and another tables containing just the Time. You can use Interval Match function to link these 2 tables and find out in which Time interval these Start/End shifts occur.
Interval Match extended syntax is used to handle the Slowly changing dimensions.
Interval Match and Slowly changing dimension
In these blogs i have made an attempt to cover all the basic building blocks of QlikView data modeling. Continue exploring and learning the awesome world of QlikView.
Further readings – Perfect your data model