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, 2019 in Others by Rahaman
• 140 points
152 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, 2019 by Rojalin
• 200 points

Related Questions In Others

0 votes
1 answer

How to use hexadecimal color strings in Flutter?

Hi@akhtar, In Flutter the Color class only accepts integers as parameters, or there ...READ MORE

answered Jul 24 in Others by MD
• 57,560 points
44 views
0 votes
2 answers

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

Its so simple.... If you want to ...READ MORE

answered Aug 12 in Others by Steve
• 200 points
177 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, 2019 in Others by Frankie
• 9,810 points
100 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, 2019 in Others by Nabarupa
403 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, 2019 in Others by Hemant Gajbe
630 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,660 points
106 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, 2019 in Power BI by anonymous
• 32,490 points
2,316 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,720 points
77 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,320 points
134 views