Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
GCIAMPA
Contributor
Contributor

Link Table

Hi all,

How can I build a link table to solve the loop in the example of Qlik Help?

Thank you.

GCIAMPA_0-1700240727782.png

 

 

Labels (1)
1 Solution

Accepted Solutions
MatheusC
Specialist
Specialist

@GCIAMPA 

I believe your topic has already had several valid responses.

Find the topic and mark the solution as accepted

Thanks



Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!

View solution in original post

5 Replies
MatheusC
Specialist
Specialist

@GCIAMPA 

Try this
I renamed the Country field in the ProductBudget table

Load
Coutry as Coutry2
ProductID
Budget
From(qvd)


I left it only linked to ProductID


Or rename the ProductID table and make the connection via Coutry, from the data set

https://help.qlik.com/pt-BR/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Scripting/associations...

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
lind_oliveira20
Partner - Contributor
Partner - Contributor

julian_rodriguez
Partner - Specialist
Partner - Specialist

 

In the scenario depicted, you have a circular reference or loop caused by the tables `Customers`, `Orders`, and `ProductBudget`, where the `Country` field exists in both `Customers` and `ProductBudget`. To resolve this, you can create a link table that consolidates the common fields, thus breaking the loop and maintaining the associations between the tables.

Here's a step-by-step guide on how to create a link table in Qlik Sense to resolve the loop:

1. **Identify Common Fields**: Determine which fields are creating the loop. In your case, it seems `Country` is the common field.

2. **Create a Composite Key**: In both `Customers` and `ProductBudget`, create a composite key that combines the fields that link to the `Orders` table. If `Country` is the only common field between `Customers` and `ProductBudget`, you can use this field alone as a key.

3. **Load the Main Tables**: Load the `Customers` and `ProductBudget` tables, creating a new field that concatenates the keys.

Customers:
LOAD
CustomerID,
Country,
CustomerName,
Country as CustomerCountry // Create a unique name for the Country field in Customers
FROM [source];

ProductBudget:
LOAD
ProductID,
Country,
Budget,
Country as ProductCountry // Create a unique name for the Country field in ProductBudget
FROM [source];

4. **Create a Link Table**: Load a new table that contains the unique keys from `Customers` and `ProductBudget`.

LinkTable:
LOAD
CustomerID,
Country as LinkCountry // Use the original field name from Customers
RESIDENT Customers;

CONCATENATE (LinkTable)
LOAD
ProductID,
Country as LinkCountry // Use the same field name as the above Country field to ensure they link
RESIDENT ProductBudget;

5. **Load the Orders Table**: Load the `Orders` table as it is, ensuring it contains the fields that link to `CustomerID` and `ProductID`.

Orders:
LOAD
CustomerID,
ProductID,
OrderID,
Quantity
FROM [source];

6. **Reload the Data**: After setting up the script, reload your data. The link table should now manage the associations, and the circular reference should be resolved.

By creating a link table (`LinkTable`), you centralize the common fields (`Country`), allowing both `Customers` and `ProductBudget` to connect to `Orders` without causing a loop. The `LinkCountry` field in the link table will be associated with both `CustomerCountry` and `ProductCountry` fields, preserving the relationships without creating a synthetic key or circular reference.

Remember to replace `[source]` with the actual data source paths or connection strings for your tables. Also, ensure that your data model accommodates these changes, as creating a link table may require adjusting your existing visualizations to use the new fields properly.

whiteymcaces
Partner - Creator
Partner - Creator

You don't need a link table. You need to rename one of the Country fields. In reality, there is no association with the country the customer is from and the country the product is from. However, your analysis requirements may include what customers and what products come from different countries or how many customers buy products in their own country versus not their own country etc. If you provide better context, we can provide more specific answers.

MatheusC
Specialist
Specialist

@GCIAMPA 

I believe your topic has already had several valid responses.

Find the topic and mark the solution as accepted

Thanks



Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!