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
1,458 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.

Master data integration and transformation with our comprehensive Informatica Course.

answered Aug 18, 2019 by Rojalin
• 200 points

Related Questions In Others

0 votes
1 answer

How to compare 2 cells with delimited items in each and output the difference in items?

The following function would do this for ...READ MORE

answered Feb 23, 2023 in Others by Kithuzzz
• 38,000 points
504 views
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, 2020 in Others by MD
• 95,460 points
816 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, 2020 in Others by Steve
• 200 points
3,574 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 7, 2019 in Others by Frankie
• 9,830 points
4,676 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
1,421 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
• 58,020 points
876 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
• 33,030 points
6,594 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,750 points
988 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,350 points
774 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP