3 Tables Realtionship with Non-Unique Keys

[#1]

Hi there

          I have an relationship problem that cannot solve yet.

          The very simple example is this one:

          Fact Table:

          Employee,

          Country,

          State,

          Town;

          Town_table:

          TownID,

          StateID,

          CountryID,

          Desc_Town;

          State_Table:

          StateID,

          CountryID,

          Desc_State;

          Country_Table:

          CountryID,

          Desc_Country;

When I try to join the tables everything seems to be fine, but the real problem comes when on a Straight Table I put the

Desc_State as Dimension and the Count of employees as Expression.

The count of every state takes all the global data , this happens because the TownID it´s  an sequence wich repeats on every change of StateID or CountryID.

Town	State	Country
1	1	1
1	2	1
2	2	1
1	3	1
1	1	2

What´s the correct way to join the tables?, Im really  stucked here.

Thanks in advance!

[krishnacbe]

Hi

Can you share some sample app and output what you are expecting

[marcus_sommer]

Maybe you shouldn't join these tables and use instead simple table-associations. If this isn't suitable and you need to merge the tables you could use mapping instead of joining to avoid unwanted elimination/duplication from data through the joining. See for this:

To Join or not to Join

Don't join - use Applymap instead

- Marcus

[anagharao]

Hi,

Please check if the fact table is joined to all the other tables (or a merged table).

I've attached an app that might be of help.

[MindaugasBacius · Accepted solution]

Do not know how the data looks but in my way of thinking I will join tables like this:

map_state:
Mapping Load
    StateID & CountryID as Key
    ,Desc_State
From State_table
;


map_country:
Mapping Load
    CountryId
    ,Desc_Country
From Country_table
;


map_employee:
Mapping Load
     TownID & StateID & CountryID as Key
     ,Employee
From Fact_table
;


Fact_table:
Load
     Applymap('map_state', StateID & CountryID, ':') as Desc_State
     ,Applymap('map_country', CountryID, ':') as Desc_Country
     ,Applymap('map_employee', TownID & StateID & CountryID, ':') as Employee
     ,Desc_Town
From Town_table
;

[jonathandienst]

That's how I would handle it myself.

[#7]

Thanks!! It works!, the only thing I changed is that  my Fact table or main was the Employee and the other 3 ones the Mapping Loads , thank you sir!