3 Replies Latest reply on Sep 14, 2020 11:51 PM by Nico Heinze

    Unconnected Lookup with condition

    Christine Choy Active Member

      I have a lookup table (lkp_general) and a customer table (customer).

       

      In the customer table consists of columns:

      - CustID

      - Name

      - Gender

      - Country

      - Race

      - Occupation

       

      While lkp_general consists of all the lookup values:

      - Keyword

      - Code

      - Description

       

      Sample data of lkp_general:

      Gender    M    Male

      Gender    F     Female

      Race       001  Chinese

      Race       002  Indian

      Race       003  Others

      Country   001   USA

      Country   002   England

      Country   003   New Zealand

       

      Question is:

      I would like to use unconnected lookup in the mapping, in the expression how can I add the condition by the keyword (if the case I would like to return the race description)?

       

      :LKP.lkp_general(race) 

       

      Thank you for your answer in advance!

        • 1. Re: Unconnected Lookup with condition
          Nico Heinze Guru

          A Lookup always needs a fixed condition. You cannot use the same LKP once for attribute 1, once for attribute 2, and once for attribute 3, the LKP simply doesn't work this way.

           

          Depending on the amount of data it may work to replace the LKP by a SQL Transformation; in this SQLT, you can use a "dynamic" parameter for the attribute to query, for example like this (assuming that the SQLT has two input ports LKP_ATTRIB and LKP_VAL):

              SELECT description AS description FROM lkp_general WHERE ~LKP_ATTRIB~ = '~LKP_VAL~'

           

          You might wander why I'm using ~~ for both values even though the lookup value is a "static" parameter.

          That's because I've seen enough cases where having both a dynamic and a static parameter within the same SQLT simply doesn't work. At least in earlier versions of PowerCenter you could not mix them, and so I've come to the habit of not trying it at all.

           

          Regards,

          Nico

          • 2. Re: Unconnected Lookup with condition
            Christine Choy Active Member

            Thank you for replying. My other colleague able to find out the solution.

             

            Just need to define other lookup condition as:

            keyword = input_keyword

            code = input_code

             

            In the expression, write as below:

            :LKP.lkp_general('Race',race)

             

            Note: where 'Race' is the attribute from lkp_general and race is the column from CUSTOMER table. 

             

            Hope this helps others too.

             

            Regards,

            Christine

            • 3. Re: Unconnected Lookup with condition
              Nico Heinze Guru

              Ah, ok, I had understood that you wanted to sometimes look up values based on KEYWORD, sometimes based on CODE, sometimes on CONDITION.

              But in your case this of course works fine.

              I should have asked more clearly what you need.

               

              Thanks for sharing and regards,

              Nico