Discussion Board for collaboration related to QlikView App Development.
Accepted Solutions
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:
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:
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.