topic Re: Link Table in Connectivity & Data Prep

Link Table

GCIAMPA — Fri, 17 Nov 2023 17:08:55 GMT

Hi all,

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

Thank you.

Re: Link Table

MatheusC — Fri, 17 Nov 2023 17:20:11 GMT

@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-between-logical-tables.htm

Re: Link Table

lind_oliveira20 — Fri, 17 Nov 2023 18:04:39 GMT

@GCIAMPA 
Follow the documentation with the example mentioned above by @MatheusC

https://help.qlik.com/pt-BR/sense/August2023/Subsystems/Hub/Content/Sense_Hub/LoadData/understand-circular-references.htm

Re: Link Table

julian_rodriguez — Fri, 17 Nov 2023 20:15:38 GMT

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.

Re: Link Table

whiteymcaces — Fri, 17 Nov 2023 21:32:58 GMT

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.

Re: Link Table

MatheusC — Mon, 20 Nov 2023 11:54:15 GMT

@GCIAMPA

I believe your topic has already had several valid responses.

Find the topic and mark the solution as accepted

Thanks