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.
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":
"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.
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.
I guess now it is correct, isnt't it?
I post how I create my LinkTable.
Distribuidor_idProvincia AS idProvincia,
Distribuidor_Distribuidor AS Distribuidor
Super_idProvincia as idProvincia,
Super_Distribuidor AS Distribuidor
DROP Fields Distribuidor_idProvincia, Super_idProvincia, Super_Distribuidor, Distribuidor_Distribuidor