A key/link table is frequently required in QlikView to resolve Synthetic Key or Circular Join issues. It can also help to tidy up a schema that has tables all over the place. The goal is to create a star (and sometimes snowflake) schema with dimension tables connected to a central key table. Unlike in classical data warehousing, the central table doesn't often have the measures - they generally stay in the dimension tables.
There are 3 rules for creating a key table. The first 2 are very straightforward - the last is where you need to use your skill and judgement to create the right result. So, here are the rules:
1. All tables should have a primary key. If a table doesn't have one unique key, derive one using a function like autonumber or autonumberhash256.
2. Break all the existing joins by renaming foreign keys (e.g. in the Orders table, rename CustomerID to O_CustomerID). For a "pure" star schema, there should be no QlikView Associations remaining and all the tables should be standalone. From a pragmatic point of view, it is fine to leave some hierarchical tables associated (e.g. Product to ProductCategory) to have a more "snowflake" schema.
3. Use a mixture of Concatenate and Join to generate the Key table using the Resident data. You will load the previously renamed foreign key with the correct name so that they connect to the right dimension table (e.g. ... O_CustomerID As CustomerID).
Find the attached example.
Regards,
Siva
.png "Luminary Alumni"){kind=icon}