Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dimension table same Id

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

3 Replies
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.

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