3 Replies Latest reply: Dec 15, 2015 1:32 AM by Jonathan Dienst RSS

    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

        • Re: Dimension table same Id
          Settu Periyasamy

          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
            Avinash R

            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
              Jonathan Dienst

              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.