topic Re: Link Table in QlikView

Link Table

kevbrown — Wed, 25 Nov 2020 16:16:04 GMT

To make a link table, do I need all the fields in the table to create one? So, I have 5 tables, calendar, person, hours, leavers and starters and I want to create a link table for them all. So, I have Date in calendar, leavers and starters and person id in person and hours. I also have person id in starters and leavers. Can I create a link field for the following and it will work?

Calendar Date AS %KeyField

Leavers Person id & Date AS %KeyField

Starters Person id & Date AS %KeyField

Hours Person id AS %KeyField#

Person Person id AS %KeyField

Re: Link Table

marcus_sommer — Tue, 21 Jun 2016 10:48:55 GMT

I'm not sure if you will get any benefit by using a link-table approach. Quite often work the normal associations between the tables very well.

- Marcus

Re: Link Table

kevbrown — Tue, 21 Jun 2016 10:49:57 GMT

I'm getting Synthetic keys

Re: Link Table

marcus_sommer — Tue, 21 Jun 2016 10:53:15 GMT

Then you need to rename some of the fields and/or combine some fields to a key. See also: Synthetic Keys.

- Marcus

Re: Link Table

Anonymous — Tue, 21 Jun 2016 10:56:33 GMT

Hi Kevin,

First, you need to concatenate the tables that are the same concept, that is, people all together, the dates and times all together ... If you have multiple types of records, generates a field that distinguishes each record.
Once you have that generates keys for each, uniting all in the same table, but do not call the keys with the same name, ie the key person name does not be equal to the key calendar name.

Besides, link tables are used to join multiple fact tables, if size is better not to do this but link them to the fact table.

Regards.

Re: Link Table

ciaran_mcgowan — Tue, 21 Jun 2016 11:08:07 GMT

Hi Kevin,

Could you throw some sample data in an excel for us? What fields are generating the synthetic keys? Is it Date?

Without seeing what you're doing, this is how I think I would approach this:

Calendar: Date AS %Key_Date

Person: PersonID AS %Key_LeaverID, PersonID AS %Key_StarterID

Hours: PersonID AS %Key_HoursID

Leavers: Date AS Leaver_Date

Starters: Date AS Starter_Date

As Marcus mentioned, you shouldn't need a link table to stop the synthetic keys as the model looks fairly straight forward, only make sure that each field is uniquely named (could be accomplished using QUALIFY/UNQUALIFY function). But if you must, the above should work.