relation between tables

[mehdibenayed]

Hello,

I am having difficulty identifying when to choose the right type of join in relation to tables. Specifically, I'm unsure when to use a left join, when to use an inner join, and when to use no join at all, letting Qlik Sense automatically match tables.

Additionally, I am struggling with understanding what to do when a relationship is one-to-one or one-to-many.

Could you please provide examples to help me learn and understand these concepts better?

Thank you.

[deepaksahirwar]

Dear @mehdibenayed ,

I would be be happy to help clarify these concepts for you.

1. Types of Joins:

Inner Join: This is used when you want to get rows that have matching values in both tables. If a record in the first table does not have a corresponding record in the second table, it is not included in the result set.

SAMPLE QUERY:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Left Join (or Left Outer Join): This is used when you want to get all rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the right side.

SAMPLE QUERY:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

2. One-to-One and One-to-Many Relationships:

• One-to-One: A row in table A can have only one matching row in table B, and vice versa. For example, in a school database, each student has one unique student ID, and each student ID is assigned to one person.
• One-to-Many: A row in table A can have many matching rows in table B, but a row in table B has only one matching row in table A.

For example, a single customer can make multiple orders, but each order is made by one customer.
When you’re deciding which type of join to use or whether to use a join at all, consider what kind of data you’re working with and what you want your final result to look like. If you want all data from one table regardless of whether there’s a matching entry in the other table, a left join would be appropriate. If you only want rows with matching data in both tables, an inner join would be the way to go.

I hope this helps! Let me know if you have any other queries.

If our response has been helpful, please consider clicking "Accept as Solution". 
This will assist other users in easily finding the answer. ‌‌

Best Regards,
Deepak

[mehdibenayed]

I have a question regarding Qlik Sense. When we keep the tables without adding any type of join explicitly, how does Qlik Sense associate the tables automatically? Could you please provide some insights into how this process works?

Thank you."

[deepaksahirwar · Accepted solution]

Dear @mehdibenayed ,

Qlik Sense excels at automatically associating tables, even without explicitly defined joins. This magical power comes from its built in functionality core engine, the Associative Engine (QIX) model. Here's a breakdown of how it works:

1. Profiling the Data:

Qlik Sense analyzes all available data tables, looking for potential connections based on commonalities. This includes:
Field names: If two tables have fields with identical or similar names, Qlik Sense considers them potential candidates for association.
Data types: Fields with matching data types (e.g., both integers) further increase the association likelihood.
Content similarity: Even with different names, Qlik Sense assesses whether the data within fields shows patterns of correlation, suggesting a possible link.

2. Scoring Potential Associations:

Based on the identified commonalities, Qlik Sense assigns scores to potential associations between tables. The higher the score, the more likely the tables are to be connected effectively. Factors influencing the score include:
Strength of data match: Exact matches in field names and values get higher scores than loose resemblances.
Cardinality: Matching fields with similar cardinalities (number of distinct values) strengthen the association.
Data distribution: Matching fields with similar data distributions (e.g., both normally distributed) add to the score.

3. Visualization of Recommendations:

Qlik Sense displays these potential associations visually in the Data Manager. Tables with high association scores are shown closer together, with green or orange lines indicating strong or moderate connections.

4. User-driven Refinement:

While Qlik Sense proposes automatic associations, you have complete control. You can:
Accept the recommendations: Drag a table with a green/orange line onto another to create the association using the suggested fields.
Create custom associations: Drag a table onto another with a red line (no recommendation), then manually choose the fields to associate.
Refine existing associations: Right-click on an association line to adjust the matching fields or define a custom key.

Additional Insights:

Qlik Sense prioritizes one-to-one relationships over many-to-one or many-to-many scenarios.
It uses a fuzzy matching algorithm, allowing for slight variations in field names and values to create associations.

You can influence the automatic process by modifying field names, data types, or even adding explicit hints in the data itself.

Overall, association feature empowers Qlik Sense's automatic you to build complex data models more efficiently, freeing you from the burden of manually defining every join. However, understanding the underlying logic and using your own analytical judgment remains crucial for optimizing your model's accuracy and performance.

I hope this helps! Let me know if you have any other queries.

If our response has been helpful, please consider clicking "Accept as Solution". 
This will assist other users in easily finding the answer. ‌‌

Best Regards,
Deepak