Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Valued Contributor II

Re: 3 Tables Realtionship with Non-Unique Keys

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

;

6 Replies
krishnacbe
Valued Contributor III

Re: 3 Tables Realtionship with Non-Unique Keys

Hi

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

Re: 3 Tables Realtionship with Non-Unique Keys

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

Re: 3 Tables Realtionship with Non-Unique Keys

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
Valued Contributor II

Re: 3 Tables Realtionship with Non-Unique Keys

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

;

MVP
MVP

Re: 3 Tables Realtionship with Non-Unique Keys

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

Re: 3 Tables Realtionship with Non-Unique Keys

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!

Community Browser