topic LinkTable in QlikView

LinkTable

vanderson009 — Fri, 06 Dec 2013 09:33:47 GMT

Hi Communty,

Why we use LinkTable??

Thanks and Regards.

Villyee

Re: LinkTable

maternmi — Fri, 06 Dec 2013 09:40:18 GMT

Hi,

from Wikipedia

junction table is a database table that contains common fields from two or more other database tables within the same database. It is on the many side of a one-to-many relationship with each of the other tables. Junction tables are known under many names, among them cross-reference table, bridge table, join table, map table, intersection table, linking table, many-to-many resolver, Link Table, pairing table, pivot table, transition table, or association table. (…)."

This definition is mostly about relational SQL databases so if we adapt it for QlikView it could be something like the following.

Link Table: It´s a table that contains common fields from two or more tables (within the same database or not).

Best regards,

Michael

Re: LinkTable

Fri, 06 Dec 2013 09:52:12 GMT

see attachment

Re: LinkTable

Fri, 06 Dec 2013 10:22:31 GMT

Link Tables are used to avoid many automatic JOIN between the tables.

if you have several fact tables, with common dimensions, without this link table, all fact tables will be inter ralated and related to the dimensions. Many synthetic keys will be formed and the too many joins will degrade performance.

By creating this link table (basically the used keys that will be related to the dimensions), you create a better model.

See the pdf, pages 16 and following.

Fabrice

Re: LinkTable

Peter_Cammaert — Fri, 06 Dec 2013 10:36:23 GMT

The answer is not so simple (there are many cases), but a simple example may shed some light on the need for a LinkTable.

Imagine you have a QlikView model that loads an Orders table and a Budget table. Both tables can be connected using multifield keys, like for example Period-Product-Region. Both tables will contain a Period field, a Region field, and a Product field. They cannot have the same name, so they will either be eliminated in one of the tables or one of those sets of fields will be translated e.g. Period & BudgetPeriod, etc.

The first solution presents a serious problem in that the table chosen to contain the dimensions will act as a filter to the other table. For example, if we choose to keep the dimensions in Orders, products for which no orders have been placed yet will show no budget.

The second solution will present a different problem. To view information from both tables, you have to make selections in two sets of different dimensions. And selections in one set of dimensions will propagate via the key field to the other set, turning it into a hassle to get decent output. And very unfriendly, un-QlikView like.

The same applies to two facts tables where each one has a data value. Which of both tables gets the connection to the calendar table?

The solution is to create a LinkTable that contains keys to both Facts tables, and all dimension fields used in these keys as well as all dimension fields used in the interface but nog in the keys (as long as they exist in both tables). In effect, you're turning your "snowflake" model inside out: dimensions move from the outside to the inside, and facts tables(s) move from the inside to the outside. And suddenly your QlikView model starts working as expected.

Hope this is clear. Otherwise, just ask.

Peter

Re: LinkTable

Tue, 02 Feb 2016 10:04:26 GMT

Hi,

Thanks for the explanation. Can you please provide a solution for the case where you mentioned:

The first solution presents a serious problem in that the table chosen to contain the dimensions will act as a filter to the other table.

Once a filter is selected, is it possible to get the data from the other 'fact' (identified via RecordType for example), according to the selected keys/dimensions?

This would be greatly appreciated.