Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Pico
Partner - Contributor III
Partner - Contributor III

Solving circular references - Workaround

Hi all, I have a problem of Circular reference, adn I'm looking for a workaround.
yes, I've already looked here but it's not that easy (at least, to me)

The situation: the dashboard is about assets assigned to persona and locations.

First table: Assets. Columns: Asset-related info, PersonID that the asset is assigned to, PlaceID that the asset is assigned to (only one is valorized, either the asset is associated to a person OR to a place, never both)

Second Table:  Persons. Column: Person.related info, AssetID that is associated to that person, Place ID where the person is.

Third Table: Places. Place-related info, AssetID that is associated to that place.

 

Another problem is that the same asset_type can be associated to a Person or a Place, and I will need to aggregate all items, so I cannot split the assets into Persons and Places table. 

how can I work around this?

Thank you very much

have a great day 

Labels (1)
4 Replies
cristianj23a
Partner - Creator III
Partner - Creator III

Hi, To work around the circular reference issue in Qlik Sense, you can use synthetic keys, resident loads, and concatenate the tables while making sure to properly handle the associations between assets, persons, and places. Here's a possible solution:

  1. Create a unique synthetic key for the Assets table by concatenating the PersonID and PlaceID fields. This will ensure that each row in the Assets table has a unique identifier.

  2. Use a resident load to create a temporary table that contains the unique keys for assets, persons, and places. This table will help manage the associations between them.

Here's an example script to achieve this:

// Load the Assets table and create a synthetic key using PersonID and PlaceID
Assets:
LOAD
*,
if(PersonID, 'Person_'&PersonID, 'Place_'&PlaceID) as AssetKey
FROM [your_assets_table_source];

// Load the Persons table and create a synthetic key using PersonID
Persons:
LOAD
*,
'Person_'&PersonID as AssetKey
FROM [your_persons_table_source];

// Load the Places table and create a synthetic key using PlaceID
Places:
LOAD
*,
'Place_'&PlaceID as AssetKey
FROM [your_places_table_source];

// Create a temporary table to store the unique asset keys
AssetKeys:
LOAD Distinct
AssetKey
Resident Assets;

// Concatenate the temporary table with Persons and Places tables
CONCATENATE(Assets)
LOAD
AssetKey,
AssetID,
Asset-related-info,
PersonID,
PlaceID
Resident Persons;

CONCATENATE(Assets)
LOAD
AssetKey,
AssetID,
Asset-related-info,
PersonID,
PlaceID
Resident Places;

// Drop the temporary table
DROP TABLE AssetKeys;

// Now, you can use the concatenated table for your analysis without circular reference issues

This script will concatenate the Assets, Persons, and Places tables into a single table based on the synthetic key, and it should help you avoid circular references while maintaining the associations between assets, persons, and places. Make sure to adjust the table names and column names according to your actual data sources.

Remember to check the results and ensure that the associations between the different entities are correctly maintained in the concatenated table. Additionally, be mindful of the potential performance impact of concatenating large tables, and consider using incremental loads if applicable.

Regarts.

https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.
Pico
Partner - Contributor III
Partner - Contributor III
Author

OK thanks, I got the point. 
This is kind of strange 'cause in this way you're gonna have one big table, and performance-wise it's going to be a problem. 

Another issue I see is the place-person and person-asset relationships being "transitive". 
Let me explain that: in the "person-related info" there is the place where it works. this place can have assets associated to the place directly. 
In this way, if I select the place, I'm gonna see both the assets associated to this place AND to the persons working there. 
Otherwise I can have a column "associated to" with either the person or the place, but then I have two fields where I can find the Place, one being "associated to" and the other one the field that links persons and the places those persons are working at.
Sorry if I'm a bit confusing, that's because I'm confused myself 🙂

marcus_sommer

In nearly all scenarios is the general approach to resolve such issues quite simple - just follow the officially recommended way to develop a data-model which is a star-scheme with a single fact-table and n dimension-tables.

It's usually not very difficult to get but it may of course need multiple measurements to concatenate the various fact-tables by harmonizing all field-names and data-structures which includes also a filling of all possible information from the other tables. There is no simpler way to get a working data-model and everything else will need more work and efforts and requiring more know how. Therefore I suggest to re-design the data-model and not to try to find workarounds for problems which are easily to avoid.

cristianj23a
Partner - Creator III
Partner - Creator III

If you follow good practices you can improve performance since qlik can support more than a billion records.

The relationships that I send you are a reference, surely some do not comply with what you indicate, for it to really work I would have to know well how the data and tables are structured, from there you can improve the relationships without affecting performance .

As marcus rightly says, you can use the star model and if you see that two tables have more than one key, see how to join it or change its name if it is not reused for another relationship.

The commands that I use the most for this type of case are join, concatenate and Mapping.

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/Mapping...

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...

https://community.qlik.com/t5/QlikView-App-Dev/Understanding-Join-Keep-and-Concatenate/td-p/328379

Regarts.

 

 

 

https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.