topic Re: Dimension table same Id in QlikView

Dimension table same Id

Mon, 07 Dec 2015 10:23:34 GMT

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

Re: Dimension table same Id

settu_periasamy — Tue, 15 Dec 2015 02:52:00 GMT

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;

Re: Dimension table same Id

avinashelite — Tue, 15 Dec 2015 06:13:51 GMT

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;

Re: Dimension table same Id

jonathandienst — Tue, 15 Dec 2015 06:31:44 GMT

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.