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: 
Not applicable

3 Tables Realtionship with Non-Unique Keys

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.

TownStateCountry
111
121
221
131
112

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

Thanks in advance!

         

1 Solution

Accepted Solutions
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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

;

View solution in original post

6 Replies
krishnacbe
Partner - Specialist III
Partner - Specialist III

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
Creator II
Creator II

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
Partner - Specialist III
Partner - Specialist III

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
Partner - Champion III
Partner - Champion III

That's how I would handle it myself.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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!