Monday, July 21, 2014

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.

No comments:

Post a Comment

web stats