How to compare 2 comna separated strings in informatica

Question

I have a requirement in informatica as below

Source is having a string column which is holding country names separated by commas as US, UK, IND, PAK

From other source data coming from lookup is having same kind of data I. e., country names separated by commas

Requirement is to generate a error code when ever there is a mismatch in the list of countries that means both the countries should match. Please find below scenarios

#1

Source1: US, UK, IND, PAK

Source2: US, UK, IND, PAK

Good record

#2

Source1: US, UK, IND, PAK

Source2: IND, PAK, US, uk

Good record, case insensitive and no need to be in same order.

#3

Source1: US, UK, IND, PAK

Source2: US, UK, IND

Bad record

#4

Source1: US, IND, PAK

Source2: US, UK, IND, PAK

Bad record

For all bad record scenarios we can populate error and come out but for Good record it should be matched with all countries available in other source and only if it is matched we need to take that record forward

Please help me on how to achieve this in informatica.

Answer 1

A java transformation can be used to compare the coloumn countries where the input ports could be SL_NO(or any primary key to identify records in source as well as lookup) and COUNTRIES of the Source table and lookup table and out ports will be SL_NO and COUNTRIES .

The lookup condition will be SL_NO=IN_SL_NO(connected lookup)

The java code is as follows:

int i,j,counter=0;
String str1[]=Countries.split(",");
String str2[]=lkp_Countries.split(",");

if(str1.length==str2.length)
 {
   for(i=0;i<str1.length;i++)
     {
      for(j=0;j<str2.length;j++)
        {
          if(!str1[i].equalsIgnoreCase(str2[j]))
           continue;
          else
           {
             counter=counter+1;
             break;
           }
        }
     }
  if(counter==str1.length)
   {
     sl_no=sl_no;
     cities=cities;
     generateRow();
   }
  else
   logError("Row having values "+sl_no+" and "+Countries+" could not be loaded as it is a bad record");
}

else logError("Row having values "+sl_no+" and "+Countries+" could not be loaded as it is a bad record");

_____________________________________________________________________________________

The logError() function is used to get the error message in the session log for bad records.

Master data integration and transformation with our comprehensive Informatica Course.