2 Replies Latest reply: Oct 12, 2017 8:10 AM by Supriya B RSS

    Dynamic key while joining tables? or any other solution?

    Supriya B

      Hello All,

       

      I have a fact table - F_PLANT

      I have dimension to derive the information - D_PLANT

       

      Unfortunately the plant dimensions have new IDs whereas the fact is updated with Old IDs only. hence there is a new table created for IDs - X_PLANT_UPDATED - which has following columns

       

      OLD_ID,

      NEW_ID,

      VALID_FROM,

      VALID_UPTO

       

      Now i have to derive information from dimension(with new ids) into fact(with old ids) using the table X_PLANT_UPDATED.

       

      Any ideas?

       

      Thanks

      Supriya.

        • Re: Dynamic key while joining tables? or any other solution?
          Jonathan Dienst

          This is best handled using interval match ("extended syntax"):

           

          // Load the interval table

          Update:

          LOAD OLD_ID,

            NEW_ID,

            VALID_FROM,

            VALID_UPTO

          FROM X_PLANT_UPDATED;

           

          // Load the fact table. Alias the ID to OLD_ID

          F_PLANT:

          LOAD ID as OLD_ID,

            DATE

            ...

          FROM F_PLANT;

           

          // Match the updates using IntervalMatch

          Left Join (F_PLANT)

          IntervalMatch(DATE, OLD_ID)

          LOAD VALID_FROM,

            VALID_UPTO,

            OLD_ID

          Resident Update;

           

          // Fetch the new ID and alias it to ID

          Left Join (F_PLANT)

          LOAD OLD_ID,

            NEW_ID as ID,

            VALID_FROM,

            VALID_UPTO

          Resident Update;

           

          // Clean up

          DROP Table Update;

          DROP Fields VALID_FROM, VALID_UPTO;


          // Now load the dimension

          D_PLANT:

          LOAD ID

            ...

          FROM D_PLANT;



          This assumes that the ID field is called "ID" date field is called "DATE". Modify the script above with the correct field names if necessary.

            • Re: Dynamic key while joining tables? or any other solution?
              Supriya B

              Thankyou for your reply Jonathan,

              As i understand , in this case we are replacing the Old IDs with New IDs matching the valid from and upto from update table , but my old ids need to stay in the table(in the ID column) since ID is joined to other facts or dimensions accordingly.

               

              Is there any way wherein i can have both columns OLD ID and NEW ID in the fact and join the dimension using a logic?

               

              *valid from and upto not much imp.