5 Replies Latest reply on Mar 24, 2021 1:42 AM by Nico Heinze

    Field comparision and populate data based on the match

    Bhargavi Nara Guru

      Hi Team,

       

      I have a requirement like

      there were 2 fields(both are strings) coming from the source table  field1, fileld2,based on these fields I have to populate the target output.

      for example:

      if fields1='AC' AND  field2='LSS' populate target_output  as 'BACKUP CAMERA' like this I have to compare every record and populate some provided value in target field.There were some thousands of records coming from source with distinct values.

      If anyone have idea how to achieve this  without manually writing big Decode statement?

       

      Thanks in advance

        • 1. Re: Field comparision and populate data based on the match
          Nico Heinze Guru

          Define a lookup table with three columsn: field1, field2, and result.

          Populate this table with records like ('AC', 'LSS', 'BACKUP CAMERA'), one record for each potential combination of field1 and field2.

          Finally use a lookup transformation in your mapping to retrieve the fitting target value for each combination of field1 and field2.

           

          Regards,

          Nico

          • 2. Re: Field comparision and populate data based on the match
            Prasenjit Saha Guru

            Hello Nico,

             

            I was also thinking about lookup, but I got stuck at the point "There are some thousands of records coming from source with distinct values.".

            If this is really 1000 distinct value, I am afraid, we cannot avoid manual decode functions even in lookup as well.

             

            But yeah, right now I cannot think of any better options than yours

             

            Regards,

            Prasenjit

            • 3. Re: Field comparision and populate data based on the match
              user126898 Guru

              To Nico's point you don't need a decode.  The lookup is a reference table that will have all the key pairs and the value column.  You populate the table once and then when you do the lookup you join on the two fields you are checking and the result is the value column.  No need to do any decode's inside of the mapping.

               

              Yes it may take some time to setup the table but it will be far easier to maintain then using a decode statement.

               

              Thanks,

              Scott

              • 4. Re: Field comparision and populate data based on the match
                Bhargavi Nara Guru

                Thanks Nico

                 

                Lookup option I have tried it worked fine, But we wanted to automate the job without loading data to some lookup table.

                Can we have option: like based on the keywords coming from source can we populate data o target?

                Kind of AI or machine learning concepts do we have in power center?

                • 5. Re: Field comparision and populate data based on the match
                  Nico Heinze Guru

                  As mentioned, the lookup is the easiest way to achieve what you need.

                  The lookup "media" (be it a relational table, a flat file, or something else which can be used as a source in PowerCenter) has to be provided once and then it should remain stable (at least until some changes in the lookup contents are needed). That's a pretty common approach for such tasks.

                   

                  What exactly do you mean by "like based on the keywords... can we populate target"? It sounds as if you are not at all comfortable with a lookup, and I would like to understand what exactly is making you feel uncomfortably.

                   

                  Regards,

                  Nico