Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Linking tables (circular reference)

Hi,

i'm trying to connect some tables and i'm getting the "circular reference". I'm not really sure how to connect them.

This is what I get right now.

Circular.png

I'm trying to connect the "t_Provincia", "t_Super" and "t_Distribuidor" and the main problem I have here is with the "idProvincia" and "Provincia".

If I connect "t_Distribuidor" with "t_Provincia":

cir2.png

So, basically:

"Provincia" field: shoul be in t_Distribuidor and t_Provincia

"idProvincia" field: should be in t_Provincia and t_Super

"Distribuidor" field: should be in t_Distribuidor and t_Super.

I was thinking about creating a new table "t_ProvinciaDistribuidor" for the "Provincia" of the t_Distribuidor table, but the main problem is that I have to crete later a chart (stacked) where I have to show per "Provincia", the total of the t_Super (in a specific status) and the Num_SuperPerDistribuidor, so in some way, I have to select a specific "Provincia" for both table in one filter.

I hope I've explained it ok.

Thanks in advance.

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

Create a snowflake model where Provincia is a central dimension attached to a link table with suitable keys. The idea is that if you can't keep dimensions on the outer layer, put them in the center.

Moreover, Povincia, Municipio and Comunidad on average can be organised into a hierarchy where only one of those is connected to the facts. That way you can create a real SnowFlake model.

Best,

Peter

View solution in original post

3 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

Create a snowflake model where Provincia is a central dimension attached to a link table with suitable keys. The idea is that if you can't keep dimensions on the outer layer, put them in the center.

Moreover, Povincia, Municipio and Comunidad on average can be organised into a hierarchy where only one of those is connected to the facts. That way you can create a real SnowFlake model.

Best,

Peter

Not applicable
Author

Hi,

    Try to give Alias name for those fields  which you don't want to link with other tables(if there is any same field name as different table field name)  then it won't show circular reference.

And Read this document  also for better understanding how to create good schema.

Regards,

Ashutosh

Not applicable
Author

Thank you for your answers.

Anyway, first of all, I apply a Mapping Load, because the "Provincia"  value in the t_Provincia and the t_Super were different.

Then, I have to make a LEFT JOIN in the "t_Distribuidor" to populate that table with the correct IdProvincia, in order to have the real id, and not the value Provincia, so I can have the same field in t_Super and t_Distribuidor.

I've have now something like this.

final.png

I guess now it is correct, isnt't it?

I post how I create my LinkTable.

LinkTable:

LOAD Distinct

    %Key1,

    Distribuidor_idProvincia AS idProvincia,

    Distribuidor_Distribuidor AS Distribuidor

Resident t_Distribuidor;

Concatenate(LinkTable)

LOAD Distinct

    %Key1,

    Super_idProvincia as idProvincia,

    Super_Distribuidor AS Distribuidor

Resident t_Super;

DROP Fields Distribuidor_idProvincia, Super_idProvincia, Super_Distribuidor, Distribuidor_Distribuidor

Thank you!