Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jim_chan
Contributor III

How to create composite key with these kind of table?

Hi guys,

i have 3 tables as per my attachment. I dont know how to create a data model with composite key and link table.

ok i have 3 tables.  and all the mentioned alphabets are the common keys for each of these tables, and there are others fields in each of the tables, i just want to list out the common fields.  Those Black means they are keys common throughout table 1,2,3. Red one only available on table B n C.

but all 3 tables have un common keys. so how many link tables and composite keys are needed?

sample_table.jpg

7 Replies
MVP & Luminary
MVP & Luminary

Re: How to create composite key with these kind of table?

I suggest before thinking about link-tables to consider if these tables could be matched with concatenate/join/mapping.

- Marcus

joey_lutes
Contributor

Re: How to create composite key with these kind of table?

Jim, I'm having a very similar problem.

I've tried 8 ways of Sunday to fix it - including a concatenate, which was not fruitful.

Here's one I got to work in a link table with 1 caveat.  Here's what I used:

Link Table in QlikView – Learn QlikView

First of all, you have 2 tables with 0 unique fields - I'm going to assume there's at least 1 in each that's unique to just that table - otherwise, kind of pointless.

The downside of this method teaches that for each table you comprise a [$Key field] consisting of 'all' foreign keys. 

(in your case, that would be:

Table 1 Key:  A&B&C&D&E&F&G&H&I as [%Key field]

Table 2 Key:  <all of them concatenated> as [%Key field]

Table 3 Key: <again, all of them concatenated> as [%Key field]

Now, this WORKS - as in, creates a link table, eliminates synthetic key and doesn't produce errors.

Obviously the downside is:  they don't match.

If you'll read the comments, they address this, kind of.  That's my caveat - I'm having trouble getting past that.

See:  Link Table for Dimension Tables Only

Hope this helps . . .

joey_lutes
Contributor

Re: How to create composite key with these kind of table?

I made some headway:

3 tables:

Items in bold link 'somewhere else' - indicating they constitute those combinations

Table1:              Table2:                Table3:

A                        A                         D

B                                              E

C                        D                         F

G                        E                         H

value                value                    value

Table1:

Load A&'|'&C as Table1_Key,  // could also use autonumberhash128 (A, C) to produce an integer

value

Resident Table1;

Table2:

Load A&'|'&C&'|'&D&'|'&E as Table2_Key,

value

Resident Table2;


Table3:

Load D&'|'&E as Table3 Key,

value

Resident Table3;


Link:

Load distinct          //distinct very important

A&'|'&C as Table1_Key,

A,

C

Resident Table 1:

Drop fields A, C from Table1; 

//don't need them in the original now as they're loaded in the Link and associated with the key field


Concatenate (Link)

Load distinct

A&'|'&C&'|'&D&'|'&E as Table2_Key,     // if you used autonumberhash128 above, use the same here

A,

C,

D

Resident Table2:

Drop fields A, C, D, E from Table2;


Concatenate (Link)

Load distinct

Load D&E as Table3 Key,

D,

E

Resident Table3;

Drop fields D, E from Table3;


This will produce a link table with no synthetic keys, and load data accordingly creating a true star schema.


NOTE: In this scenario (similar to my own) you'll notice Table3 does not have a direct association with Table1.  I'm noticing that in the UI, they're not associating at all for some reason (unless table B is involved).  Not sure if that's design (bad) or a bug in my system.


Hope that helps!






joey_lutes
Contributor

Re: How to create composite key with these kind of table?

One more note -

The Link table code assumes  you did indeed load the linked fields in their respective original tables (thus the 'drop' statements)

Good ref doc:  how to create composite key

kalyanivintha1
Contributor II

Re: How to create composite key with these kind of table?

In this Type of scenarios its better to append the tables

by using joins or concatenation

joey_lutes
Contributor

Re: How to create composite key with these kind of table?

Hi Kalyani,

I did that as well with my own scenario using joins.

It produced a massive table with 60M+ rows when the source data was 5k or less.  The load time was forever and the application was extremely sluggish.  It was ineffective, where the link table has very good performance (with the exception of the aforementioned association issue . . . ) loading only hundreds of rows.

kalyanivintha1
Contributor II

Re: How to create composite key with these kind of table?

hooo k

i got some new point...Thanq joey.