topic Re: 3 Tables Realtionship with Non-Unique Keys in QlikView

3 Tables Realtionship with Non-Unique Keys

Thu, 19 May 2016 17:44:00 GMT

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!

Re: 3 Tables Realtionship with Non-Unique Keys

krishnacbe — Thu, 19 May 2016 18:29:06 GMT

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

Re: 3 Tables Realtionship with Non-Unique Keys

marcus_sommer — Fri, 20 May 2016 06:37:22 GMT

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

Re: 3 Tables Realtionship with Non-Unique Keys

anagharao — Fri, 20 May 2016 07:49:01 GMT

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.

Re: 3 Tables Realtionship with Non-Unique Keys

MindaugasBacius — Fri, 20 May 2016 08:22:04 GMT

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

;

Re: 3 Tables Realtionship with Non-Unique Keys

jonathandienst — Fri, 20 May 2016 08:23:42 GMT

That's how I would handle it myself.

Re: 3 Tables Realtionship with Non-Unique Keys

Fri, 20 May 2016 14:26:08 GMT

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!