topic Re: Modeling n:m relationships in QV in QlikView

Modeling n:m relationships in QV

Tue, 30 Apr 2013 05:25:14 GMT

Hi - apologies if this has already been discussed at length (I would imagine it being a pretty common question, but didn't see anything obvious on a search of the forum)...

I have 2 logical tables:

Companies

----------------

CompanyID | a bunch of other info

Partnerships

------------------

CompanyID | PartnerID

The Partnerships table is a standard n:m linking table. Both CompanyID and PartnerID should match Companies.CompanyID. What is the best way to model this in QV. I did the default "generate CSV for both of these and just see what happens" thing, and noticed that QV created a synthetic key for what appeared like joining on both CompanyID and PartnerID - and I'm pretty sure this is not what I want. Any pointers or links to resources are greatly appreciated.

_howard

Re: Modeling n:m relationships in QV

sushil353 — Tue, 30 Apr 2013 05:32:59 GMT

Hi,

to remove the synthetic key you can rename the fields...

or you can create the composite key.. it depends on your data model

here you want all the data from partnership table rt?

so ..

Partnerships

PartnerID & CompanyID as key,

.....

.....;

Companies

----------------

PartnerID & CompanyID as key

... if i that not works then post some sample data.

HTH

Sushil

Re: Modeling n:m relationships in QV

sujeetsingh — Tue, 30 Apr 2013 05:34:38 GMT

Hi Howard,

Here is a blog which will help you a lot

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

Re: Modeling n:m relationships in QV

Tue, 30 Apr 2013 06:12:51 GMT

firstly

Synthetic key :-- When between two table 2 or more than 2 fields are common, then Synthetic key are made.

Now--

there are three ways to remove Synthetic key.

1) JOIN

2) CONCATENATE

3) Allias (that is rename the fields)

On the basis of these above , you can made the best modelling.

example--

load a,

from tableA;

join

load a,

from tableA;