Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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.