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.