Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
suren946
New Contributor III

Relationship between tables in Data Modelling

How to find the relationship between two table is one-to-one or one-many or many-to-many?

I have found below link but unable to understand, could someone explain in detail

data model relationships | Qlik Community

1 Solution

Accepted Solutions
suren946
New Contributor III

Re: Relationship between tables in Data Modelling

Hi Daniel,

Thank You for clear explanation.

View solution in original post

4 Replies
isingh30
Valued Contributor

Re: Relationship between tables in Data Modelling

suren946
New Contributor III

Re: Relationship between tables in Data Modelling

hi  Ishtdeep,

I have already found this link but unable to understand. so looking for more information

daniel_rodrigue
New Contributor III

Re: Relationship between tables in Data Modelling

Hello, suren946!

Suppose you have these two tables below:

Countries:

Load * Inline

[ID, Country

1, EUA

2, Brazil

3, Japan

];

Cities:

Load * Inline

[City, ID

Sao Paulo, 2

Tokyo, 3

New York, 1

];

The relationship between them is clearly one-to-one, but you can prove that by creating a third table with ID's from the first one and the count of occurrences of those ID's on the second table, like below:

IDsCount:

NoConcatenate

Load ID

Resident Countries;

Left Join

Load ID,

         Count(ID) As IDCount

Resident Cities

Group By ID;

Result:

Capturar.PNG

As there is no record of IDCount greater than 1, we know the relationship between "Countries" and "Cities" tables is one-to-one.

Now, if we make this change to the second table:

Cities:

Load * Inline

[City, ID

Sao Paulo, 2

San Diego, 1

Tokyo, 3

Rio de Janeiro, 2

New York, 1

];

And run script again, we end up with this result:

Capturar 2.PNG

As now we have IDCount's greater than 1, the relationship between tables is one-to-many.

And from now, if we invert the search, counting the ID's from the second table on the first one, like this...

IDsCount:

NoConcatenate

Load ID

Resident Cities;

Left Join

Load ID,

         Count(ID) As IDCount

Resident Countries

Group By ID;

...and find IDCount's >1, we actually have many-to-many relationship tables.

suren946
New Contributor III

Re: Relationship between tables in Data Modelling

Hi Daniel,

Thank You for clear explanation.

View solution in original post