QV4 Link Tables and concatenate tables

Link Tables and concatenation of tables are used to remove synthetic keys or circular references.

Concatenate - To apply concatenate we should first know about the synthetic keys.

Synthetic keys
When two or more tables share common fields, Qlikview automatically create synthetic key between them.
Synthetic key is the combination of common fields.

AS a example we have two table employeeA and B , which have common fields except SSN and BankAcctNo.

[EmployeeA]:
Load * INLINE [
ID, Emplyoee, Country, City, Zip, Address, SSN
1, Amit, INDIA, CHANDIGARH, 160011, abc, 34544];

[EmployeeB]:
Load * INLINE [
ID, Emplyoee, Country, City, Zip, Address, BankAcctNo
1, Amit, IN, CHD, 160011, abc, SBI2561];

How to Concatenate:
Concatenate just append the data of second table into the first table.
By default QV concatenate the table, which was just before loaded.

example
[EmployeeA]:
Load * INLINE [
ID, Emplyoee, Country, City, Zip, Address, SSN
1, Amit, INDIA, CHANDIGARH, 160011, abc, 34544];

concatenate
Load * INLINE [
ID, Emplyoee, Country, City, Zip, Address, BankAcctNo
1, Amit, IN, CHD, 160011, abc, SBI2561];

we can also use concatenate (EmployeeA) , it means the table can concatenate on any level of script to this particular table. It doesn't need to put the command after load table.
[note be careful tablename and field name are case sensitive in qlikview]

click preview the table from datamodel (ctrl+t). We can objserve that the data of second table is appended with table1. where SSN and BanAcct is non matching fields so that non-matching fields have all been given null values.

Link Tables

comming soon

QV3 synthetic keys

Synthetic keys
When two or more tables share common fields, Qlikview automatically create synthetic key between them.
Synthetic key is the combination of common fields.

AS a example we have two table employeeA and B , which have common fields except SSN and BankAcctNo.

[EmployeeA]:
Load * INLINE [
ID, Emplyoee, Country, City, Zip, Address, SSN
1, Amit, INDIA, CHANDIGARH, 160011, abc, 34544];

[EmployeeB]:
Load * INLINE [
ID, Emplyoee, Country, City, Zip, Address, BankAcctNo
1, Amit, IN, CHD, 160011, abc, SBI2561];

now as u can see in picture the synthetic key has been created with name "$Syn .." , and it is the combination of common fields between two tables.

There are several methods we can use to remove synthetic keys:
• We can rename those fields that are a part of the synthetic key but should not be a part of the association between the two tables.
• We can remove conflicting fields from one of the two tables. To remove a field, we just erase the corresponding line of code from the Load script.
• We can create an explicit complex key with the concatenation of all common fields that actually represent the link between the two tables.
    °° After creating the new complex key, we can remove the conflicting fields from either table.

QV2 QVD datafiles

QVD is a flat data file with the extension .qvd. It can store a single table of data. It is created at path same as load script of a QVW file.

The structure of the file is essentially an XML format, with various bits of header information stored at the top of the file and the rest of the data beneath. The property of QVD file is that it is compressed on creation using the same algorithms as QlikView uses to store data in memory – so the files can be incredibly small for the amount of data they contain. Loading data from QVD into memory is blindingly fast as the format of the file mirrors how QlikView addresses data in RAM.

A QVD file holds exactly one data table and consists of three parts:

1. A well formed XML header (in UTF-8 char set) describing the fields in the table, the layout of the subsequent information and some other meta-data.
2. Symbol tables in a byte stuffed format.
3. Actual table data in a bit-stuffed format.

WHY QVD

If the data being collected is coming from multiple systems or is being distributed via multiple QlikView applications. QVDs can fulfil this requirement admirably. QVDs are also essential when you want to adopt an incremental load strategy.

Increasing Load Speed

By buffering non-changing or slowly changing blocks of input data in QVD files, script execution becomes considerably faster for large data sets.

Decreasing Load on Database Servers

The amount of data fetched from external data sources can also be greatly reduced. This reduces work load on external databases and network traffic. Furthermore, when several QlikView scripts share the same data it is only necessary to load it once from the source database into a QVD file. The other applications can make use of the same data via this QVD file.

Consolidating Data from Multiple QlikView Applications

With the Binary script statement it is possible to load data from only one single QlikView application into another one, but with QVD files a QlikView script can combine data from any number of QlikView applications. This opens up possibilities e.g. for applications consolidating similar data from different business units etc.

Incremental Load

In many common cases the QVD functionality can be used for facilitating incremental load, i.e. exclusively loading new records from a growing database.

To learn more about the use of QVD files and Incremental Load, follow the link below:

Using QVD Files for Incremental Load

How to create QVD file

QVD files can be created by two different ways:

• using STORE command

Typically QVDs are created during the execution of your QlikView load script (*.qvw file). A STORE statement writes the current contents of a single table in your data model to a file on the disk. The syntax for this is:
STORE TableName INTO ..\Data\FileName.qvd (QVD);
example:

dimemp:

LOAD "ename","job",deptno;SQL SELECT *
FROM scott."public".emp;

Store dimemp into dimempQVD.qvd;

• By preceding a load or select statement with the Buffer prefix, QlikView will automatically create a QVD file which under certain conditions can be used instead of the original data source when reloading data.

A BUFFER prefix on a LOAD or SQL statement creates and maintains an automatic QVD for that statement. executions of the LOAD/SELECT statement will read from the QVD, avoiding read data from database. A read from QVD is generally faster than fetching from database.

dimemp:
BUFFER LOAD "ename","sal","job","deptno";SQL SELECT * from scott."public".emp

On first execution, the SELECT will fetch rows from the database and the resulting dimemp will be stored in a specially named QVD on the local machine(path preferabley in user/appdata). After this dimemp will automatically be loaded from the local QVD.

If you make a change to the dimemp LOAD/SQL statement, QV Reload will detect the change and fetch from the database again and update the local QVD.

QV1 Inline Table

We can load data into qlikview by various ways such as
- Load data from different type of DataSource (RDBMS or Nosql db),
- Load Data from Flat file,
- Load Data from SAP interface.

Another type of loading is we can directly type the table script in script window.

Syntax
Open script window with the help of  ctrl+e or click on icon.

[counting]:
LOAD * INLINE [
ID, Description
1, One
2, Two
3, Three
4, Four
5, Five
6, Six
7, Seven
8, Eight
9, Nine
10, Ten
];

Now counting table have two columns id and description