How to compare 2 comna separated strings in informatica?

+2 votes
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.
Aug 16 in Others by Rahaman
• 140 points
59 views

1 answer to this question.

+3 votes
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.
answered Aug 17 by Rojalin
• 200 points

Related Questions In Others

0 votes
1 answer

How to get the URL of the current tab in Google Chrome?

There are different ways of doing this:- You ...READ MORE

answered Dec 21, 2018 in Others by Nabarupa
89 views
0 votes
1 answer

What is a name function in JavaScript & how to define it?

A named function declares a name as ...READ MORE

answered Mar 6 in Others by Frankie
• 9,810 points
38 views
0 votes
1 answer
0 votes
1 answer

How to unistall a file in Linux?

You can uninstall a file using the ...READ MORE

answered Mar 8 in Others by Nabarupa
109 views
0 votes
0 answers

How to handle large http response data from observer in Angular application to avoid browser crash?

Suppose we have a angular application which ...READ MORE

Apr 18 in Others by Hemant Gajbe
167 views
0 votes
1 answer

How to store an array in localstorage?

Localstorage only supports Strings. So you can ...READ MORE

answered Jul 1 in Others by sunshine
• 1,240 points
1,081 views
0 votes
1 answer

How to allow the application to communicate back to the on premise equipment?

The connection would need to be either ...READ MORE

answered Aug 16, 2018 in AWS by Priyaj
• 57,300 points
42 views
+2 votes
1 answer

How to export data to CSV from power bi embedded url ?

Hi Arathi, You can open Visuals from embedded ...READ MORE

answered Aug 23 in Power BI by anonymous
• 32,260 points
334 views
0 votes
1 answer

Selection Bias

Selection bias is the bias introduced by the ...READ MORE

answered Jul 11, 2018 in Data Analytics by CodingByHeart77
• 3,690 points
40 views
0 votes
1 answer

Using Real Time flume Data for Analysis

By using MorphlineSolrSink we can extract, transform ...READ MORE

answered Jul 17, 2018 in Database by kurt_cobain
• 9,280 points
74 views