Synthetic keys
When there is more than one field that link two tables, you will get a synthetic key in QlikView. There are several ways to solve the problem with synthetic keys. Which solution that is most suitable depends on the data structure of the tables you are loading.
Exercise 1:
Open a new QlikView document and load the files Invoices1.xls and Budget.xls.
Use the table viewer to see that you now have a synthetic key that consists of the fields Period, Salesman and ArticleID.
Concatenate the two tables to get rid of the synthetic key. You can concatenate tables in two ways:
The concatenate method can be used if all related dimension fields exist in both tables.
Exercise 2:
Load the file Invoices2.xls instead of Invoices1.xls. The file Invoices2.xls contains one more field: SalesTeam.
Create a straight table that shows Amount and Budget per SalesTeam. You will notice that there is no connection between Budget and SalesTeam. This is because the field SalesTeam does not exist in the file Budget.xls.
Create a composite key field by concatenating the fields Period, Salesman and ArticleID. Load the fields Period, Salesman and ArticleID from the invoice table, but remove them from the budget table where you load only the composite key field. Remove the "concatenate load" from the previous exercise.
Tip: Use the autogenerate ) function when you create the composite
key field.
The composite key method can be used when there are different dimension fields in the tables and at least one of the tables contains all values of the key fields.
Exercise 3:
Load the file Invoices3.xls instead of Invoices2.xls. The file Invoices3.xls contains invoices only from period January, February and March. It also contains an invoice made by a new sales person, Claire, who does not exist in the budget file.
As you notice it is not possible to analyze the budget figures for April, as the field "Period" is not loaded from the budget table.
Change the script so that you load Period, Salesman and ArticleID from the budget table instead of the invoice table. As you notice it is now impossible to analyze the sales figures for Claire, as she exists only in the invoice table but we load the field Salesman from the budget table where she does not exist.
Create a link table with the fields Period, Salesman and ArticleID. The link table will connect to the invoice table and to the budget table via the composite key field that you created in the previous exercise.
The link table method can be used when there are different dimension fields in the tables but none of the tables contains all values of the key fields.
Circular references
When you have a data structure with three or more tables that connect in a "circle" you will run into a circular reference in QlikView. Circular references should always be avoided.
Exercise 4:
Use your QlikView document from the previous exercise and load the file SalesTeam.xls. When you reload the document you will get an error message saying that one or more loops have been detected in the database structure.
Use the table viewer to analyze what has happened in the data structure and find a way to avoid the circular reference.
|
