Monday, July 21, 2014

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

No comments:

Post a Comment

web stats