Strategy for creating Key Tables

    What is a Key Table?
    A key table is a central table that stores all the relationships between the primary key values. Within QlikView, they are quite easy to build using a combination of Join and Concatenate.

    Each other table in the Schema, with some exceptions, is linked to the central key table and to the Key Table only. The tables linked to the Key Table are generally called "Dimension Tables".

    The exceptions are generally some lookup tables that are not really relevant to have associated with other tables. For example, in the schema above, we have Division linked to Company. It is not really appropriate or necessary to bring the Division ID into the Key Table (although it is possible) so we can leave it out and linked to Company. Another example is the Category link to Product.

    When all the tables are linked via the Key Table alone, this is generally called a "Star Schema". When we have other tables that remain linked off the main dimension tables (such as Division or Category), is is often called a "Snowflake Schema".

     

    Strategy

    There are some simple steps:

    1. Ensure that each table has a primary key defined.

    Create a composite key if necessary.

    2. Break all the existing relationships.

    This can be achieved by:

    a. renaming the Foreign Keys in each table (my preferred method), or

    b. implementing QUALIFY in your QlikView script - probably with an UNQUALIFY "Join_*" statement.

    3. Starting with a table that has several foreign keys, create a KeyTable and then either:

    a. Use JOIN to add additional keys, or

    b. Use CONCATENATE to add additional rows.

    4. Look for opportunity to create "Flag" fields to indicate what type of record this is. For example, we may have an Order date and a Ship date in our Key table - we should create a DateType field with a static value of either "Ship" or "Order" to indicate what type of date we are looking at.

    That it! Simple, eh?