Qlik Community

QlikView Documents

Documents for QlikView related information.

Generic keys

Generic keys

Generic Keys is a way to define keys between tables in a more general way so that their values can represent other things than individual key values; they can represent groups of key values or any key value. As an example, you can combine product IDs, product group IDs and a symbol for all products into one key field.

You can use generic keys to solve many data modeling problems:

  • Authorization table with OR-logic between fields
    If you have an authorization table you sometimes want to have a slightly more complex access restriction than a simple logical AND between fields. It could be e.g., that a user is allowed to see sales for all regions for a specific product and at the same time the European sales for all products. Generic keys can be used here.
  • Mixed dimensional granularity in a single fact table
    Often you want to compare actual numbers with budget numbers. The standard method is to concatenate these two tables into one common fact table. However, this new fact table could have mixed granularity in many of the dimensions. Generic keys can be used here.
  • Multiple fact tables linked using a master link table
    Sometimes you have fact tables that are so different that you don’t want to concatenate them. To solve this problem you can make a data model that has a central link table and uses generic keys.

See more in the attached files.

HIC

PS I have been asked to make an example on comparing budget and actual numbers, so here it comes as a second attachment. It is a zip file with a mock-up order database with a budget. Create a new empty qvw; expand the zipped files in the same folder as the qvw; insert one of the two included script files into the qvw and run the script.

Attachments
Comments
kavicsgym
Contributor

Hi Henric,

First of all, a big thank you for the great posts, we learn so much from them!

Second, I have a case when the fact table is on 3 (or 4) different dimension levels (beside daily data, we load precalculated kpi-s from a cube...).

From geo point of view, sales data are either by sales unit, by city, by county, or country level (null for sales unit, city and county).

From time point of view, there are daily, weekly (Yearweek) and monthly (yearmonth) data.  (And a mixture of these in the Fact)

My question is related to the weekly data, how to include it in the generic dimension concept, when weeks cannot directly be groupped into months (or a certain week, e.g. 2017.6th week belongs to more than one months)? Days can separately be groupped into YearWeek or Month....

Thanks for your reply and time!

0 Likes

Strictly speaking, it is not possible to group weekly data and monthly data together like that.

Since a week can belong to several Gregorian months, the Date-Week-Month-Year structure isn't really a hierarchy. Rather, I'd say that the calendar has two different hierarchies:

  • Date-Week-WeekYear
  • Date-Month-Year

So, you can choose to use one of the two. For your data, it would mean that monthly aggregates cannot be attributed to the first hierachy, and weekly cannot be attributed to the second hierarchy.

Or you can re-define what you mean by "Week" or "Month", by using e.g. Broadcast months or 445-periods. In both of these, a week belongs uniquely to one "Month", but the months differ from the Gregorian months. For example, BroadCast weeks always belong to the month of the last day of the week. See https://community.qlik.com/docs/DOC-16939

Can you redefine the fact table to use other months than Gregorian months?

0 Likes
kavicsgym
Contributor

Dear Henric, thanks a lot for the very quick reply.

I have to look into the proposed different calendars and discuss if we can use one of them. Thanks again!

0 Likes
kavicsgym
Contributor

Hi Henric,

In the end, we have opted for a rather simple solution of plotting monthly data to the first day of the month and weekly data to the first day of the week, thus turning them into 'daily' data. Can you think of any side effects of this or any reasons against it? Thanks!!

0 Likes
Juniper
New Contributor

Hi Henric, thanks a lot for this paper! I have a short question on the third example (the pharmaceutical one). I would like to know for what reason you use inclusive generic symbols in your generic key tables? Aren't we in a situation closed to "actual numbers compared to budget numbers"?
Especially for the %SalesReps table I would expect that the sales representatives disappear when the user selects  specific facts of the Sales table. 

Thanks in advance

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2012-10-03 02:24 AM
Updated by: