Dimension table same Id

[#1]

I have 2 dimension table with same field name

Fact table

- ORIGIN_AIRPORT_ID

- DEST_AIRPORT_ID

Dimension table

AirportID

- code

- description

How to link the fact table to the dimension table? I did the below:

Fact table

- ORIGIN_AIRPORT_ID as AirportID

- DEST_AIRPORT_ID as AirportID

Dimension table

AirportID

- code as AirportID

- description

[settu_periasamy]

Hi,

May be you can create two Dimension Table Like

Fact_Table:

Load ORIGIN_AIRPORT_ID,

        DEST_AIRPORT_ID

From Source;

Origin_Airport_Table:

Load code as ORIGIN_AIRPORT_ID,

        description  From Origin_AirportID_Source;

Dest_Airport_Table:

Load code as DEST_AIRPORT_ID,

        description  From Dest_AirportID_Source;

[avinashelite]

In Qlikview you should always link two table by a single key other wise it will form a synthetic key ...In your case try like this .

Fact_Table:

Load ORIGIN_AIRPORT_ID,

        DEST_AIRPORT_ID

From Source;

Dimension table

AirportID

- code

- description

ORIGIN:

LOAD ORIGIN_AIRPORT_ID as AirportID,

// rename rest of the column as Origin_column1

Resident

Fact_Table;

DEST:

LOAD DEST_AIRPORT_ID as AirportID,

// rename rest of the column as Dest_column1

Resident

Fact_Table;

[jonathandienst]

There are a number of ways of solving the problem. The three most common would be:

1. Load the dimension multiple times:

LOAD AirportID As ORIGIN_AIRPORT_ID

  Code,

  Description

FROM Airports;

LOAD AirportID As DEST_AIRPORT_ID

  Code,

  Description

FROM Airports;

2. Create link table

This assumes that you have some sort of fact unique key (FactKey in the example below):

Fact:

LOAD

  Factkey,

  ORIGIN_AIRPORT_ID,

  DEST_AIRPORT_ID,

  ...

FROM ...

Link:

LOAD Distinct

  Factkey,

  ORIGIN_AIRPORT_ID As AirportID,

  'Origin' As Type

Resident Fact;

LOAD Distinct

  Factkey,

  DEST_AIRPORT_ID As AirportID,

  'Origin' As Type

Resident Fact;

DimAirport:

LOAD AirportID,

  Code,

  Description

FROM Airports;

3. Join or Map into Fact table

MapCode

Mapping LOAD AirportID,

  Code,

FROM Airports;

MapDescription:

Mapping LOAD AirportID,

  Description

FROM Airports;

Fact:

LOAD

  Factkey,

  ORIGIN_AIRPORT_ID,

  DEST_AIRPORT_ID,

  ApplyMap('MapCode', ORIGIN_AIRPORT_ID) As Orig_Code,

  ApplyMap('MapCode', DEST_AIRPORT_ID) As Dest_Code,

  ApplyMap('MapDescription', ORIGIN_AIRPORT_ID) As Orig_Desc,

  ApplyMap('MapDescription', DEST_AIRPORT_ID) As Dest_Desc,

  ...

FROM ...

The one to choose would depend on what sort of analysis you need to perform.