Matching Data Between Two Sources Using Part of a String

Matching Data Between Two Sources Using Part of a String

The illustration below explains different scenarios to match data between two sources using part of a string.

Let us understand the requirement with an example. Click on each image if needed to open a larger view of the image.

The MASTER_LIST source contains a list of strings that need to be searched against the BANK_LIST source. The two sources are shown below.

For instance, if the search string is 'INDIA' in the MASTER_LIST source, the matching rows in the BANK_LIST source will be 'Indian Bank' and 'STATE BANK OF INDIA' since both bank names contain the string 'INDIA'. The resultant rows will be loaded into the BANK_MASTERtable as shown below.

The matching between the two sources should not be case-sensitive.


Scenario 1: When both the sources are relational tables and reside in the same database. The database for this example is Oracle.

The INSTR function returns the location of a substring in a string. If the INSTR function returns '0', it implies, the substring is not present in the original string. A SQL query that matches the data between the two sources is given below.

SELECT
mstr.master_id,
bank.bank_id,
bank.bank_name
FROM
master_list mstr, bank_list bank
WHERE
INSTR(bank.bank_name, mstr.search_string) > 0

The above query returns only one row as shown below.

The query should not be case-sensitive and it can be re-written as below by converting both the string (bank.bank_name) and the substring (mstr.search_string) to lower case. This query should be added to the Sql Query section in the Source Qualifier properties.

SELECT
mstr.master_id,
bank.bank_id,
bank.bank_name
FROM
master_list mstr, bank_list bank
WHERE
INSTR(LOWER(bank.bank_name), LOWER(mstr.search_string)) > 0

The mapping is a simple pass-through mapping as shown below.

The final output will be the same as shown in the BANK_MASTER table below with the desired four resultant rows.

Scenario 2: When both the sources are flat files or one source is a flat file and the other is a relational table.

In this example, both the sources are flat files. The mapping implementation is shown below.

After, the Source Qualifier transformations, create two expression transformations as shown above. Create two output ports MASTER_KEY and BANK_KEY in the expression transformations EXP_Master_List and EXP_Bank_List respectively and in the expression editor pass the integer value '1'. These values will serve as a dummy join to merge the rows from both the flat files in the joiner transformation.


In the joiner transformation, designate the MASTER_LIST source as the "Master" source since it has fewer rows as compared to the BANK_LIST source. The Joiner condition is shown below. The Join type is "Normal Join".

The joiner transformation essentially does a full outer join i.e. all the rows between the two sources are matched with each other. We need to select only those rows that meet the defined criteria. This is achieved by using a filter transformation with the Filter Condition as shown below.

Scenario 3: When both the sources are relational tables but reside in different databases.



A similar approach to Scenario 2 can be used in this case too, but it would mean joining all the rows between the two source tables in the joiner transformation. Rather we can issue a query to the BANK_LIST table similar to the query in Scenario 1 except that the query won't have the MASTER_LIST table since it is in a different database.


The SQL transformation can be used to process queries midstream and to get the matching rows from the BANK_LIST table.



The source definitions for MASTER_LIST and BANK_LIST tables are shown below. Both the tables are in separate databases and there exists no DB links either between the two databases.

The BANK_MASTER target definition is shown below.

Create a new mapping. Drag the MASTER_LIST source definition and BANK_MASTER target definition into the Mapping Designer workspace as shown below.

Create a SQL transformation SQL_Get_Bank_Details as shown below. Click Create to proceed.

Proceed with the default settings as shown below. Click OK and Done to continue.

The SQL transformation needs to be run in the Query Mode since the SQL queries issued to the BANK_LIST table will be dynamic i.e. since the MASTER_LIST table contains two search strings 'INDIA' and 'AMERICA', two queries will be issued as given below.



SELECT BANK_ID, BANK_NAME
FROM
BANK_LIST
WHERE
INSTR(LOWER(BANK_NAME), LOWER('INDIA')) > 0;


SELECT BANK_ID, BANK_NAME
FROM
BANK_LIST
WHERE
INSTR(LOWER(BANK_NAME), LOWER('AMERICA')) > 0;


Drag the MASTER_ID and SEARCH_STRING ports from the Source Qualifier to the SQLtransformation as shown below.

Double click on the SQL transformation to edit it. Go to the SQL Ports tab. Uncheck the SEARCH_STRING as an output port since it is not required in the target as shown below. Only the MASTER_ID needs to be passed to the target, so it remains as an Input/Output port.

Add two SQL output ports BANK_ID and BANK_NAME as shown below ensuring that the correct Native Type and Precision are selected for each.

Next click on the section highlighted in red above to open the SQL Editor that will contain the SQL query that gets issued midstream. Type the query as shown below. Ensure that the order of the fields in the SELECT clause match the order of the SQL output ports.

Now, since the 'SEARCH_STRING' needs to change dynamically as shown in the two queries above, we need to use String Substitution. Click on the SEARCH_STRING port below String Substitution to add it to the query as shown below.

Modify the query as shown below, so that it matches the above two queries that need to be issued to the BANK_LIST table.

Click OK to continue. Link the MASTER_ID_output, BANK_ID and BANK_NAME ports from the SQL transformation to the target definition. The complete mapping is shown below.

In the session task, mention the correct relational connections. A relational connection (Database_B) needs to be specified for the SQL transformation too. As shown below, the MASTER_LIST table is in Database_A, BANK_LIST table is in Database_B and BANK_MASTER target table is in Database_C.

Concatenating multiple fields from the source table as comma separated values into a field in the target table

Concatenating multiple fields from the source table as comma separated values into a field in the target table

The purpose of the mapping discussed below is to concatenate the employee skills data available in the EMPLOYEE_SKILLS source table and load it into the EMPLOYEE_SKILL_SUMMARY target table.

Below is the script for EMPLOYEE_SKILLS source table:

CREATE TABLE EMPLOYEE_SKILLS

(EMP_ID NUMBER(10),

EMP_NAME VARCHAR2(30),

EMP_SKILL1 VARCHAR2(40),

EMP_SKILL2 VARCHAR2(40),

EMP_SKILL3 VARCHAR2(40));

The data available in the EMPLOYEE_SKILLS table is shown below:

Below is the script for EMPLOYEE_SKILL_SUMMARY target table:

CREATE TABLE EMPLOYEE_SKILL_SUMMARY

(EMP_ID NUMBER(10),

EMP_NAME VARCHAR2(30),

EMP_SKILLS VARCHAR2(120));

The data from EMP_SKILL1, EMP_SKILL2 and EMP_SKILL3 columns of EMPLOYEE_SKILLS table should be concatenated as comma separated values into the EMP_SKILLS column of the EMPLOYEE_SKILL_SUMMARY table.

The mapping shown above is the earlier mapping which concatenated all the skill Input port values in the EMP_SKILL_OUT Output port in the EXP_CONCAT_SKILLS Expression Transformation. The expression used in the EMP_SKILL_OUT port is shown below.

The issue surfaced after the data was loaded in the target table. Below are the records loaded into the EMPLOYEE_SKILL_SUMMARY table.

Now, the EMP_SKILLS field was displayed in some analytical reports and the way the data showed up didn’t look very pleasing. For instance, EMP_ID = 1199 has no skills in the EMPLOYEE_SKILLS source table, so when the three input port values were concatenated in the expression transformation, the data displayed in the reports was ',,'. So, a logic needed to be implemented that took care of NULL values and didn’t add a ‘comma’ for the NULL values.

The new logic is discussed below.

Instead of concatenating all the EMP_SKILL1_IN, EMP_SKILL2_IN and EMP_SKILL3_IN port values together, the new logic checked if the Input Skill values in the ports EMP_SKILL1_IN and EMPSKILL2_IN were having NULL values. If it is a NULL value, it will pass a blank string as '', else it would concatenate the incoming value with a ‘comma’ in the variable ports EMP_SKILL1_V and EMP_SKILL2_V.

The EMP_SKILLS_CONCAT_V variable port will concatenate values from the variable ports EMP_SKILL1_V, EMP_SKILL2_V and the input port EMP_SKILLS3_IN.

Now, the only problem of a ‘comma’ occurring at the end of the concatenated string will be in a scenario when EMP_SKILL2_IN and EMP_SKILL3_IN values are NULL or when EMP_SKILL3_IN values is NULL. For instance, if we observe in the source table EMPLOYEE_SKILLS, for EMP_ID = 1127, the EMP_SKILL1 and EMP_SKILL2 columns have valid data. So, for this particular record, the EMP_SKILLS_CONCAT_V port will have the string value as ‘SQL Server 2005,OBIEE 10.1.3.4,’. At the end of the string, the extra comma gets added as well. 

To eliminate this ‘comma’, the string in the variable port EMP_SKILLS_CONCAT_V is reversed in the variable port EMP_SKILLS_REVERSE_V. The EMP_SKILLS_REMOVE_COMMA_V variable port will check for the occurrence of a ‘comma’ at the start of the data string. If it encounters a comma, it will pass only the substring without the ‘comma’ at the start of the string, else it passes the data as it is. This is achieved with the expression as shown below.

Now, to get back the original concatenated string without any extra ‘comma’ , reverse the data again and pass it through the EMP_SKILLS_OUT output port to the final target table column EMP_SKILLS.

These are the records in the EMPLOYEE_SKILL_SUMMARY table after running the modified mapping again.

Reporting the difference rows between two sources using Informatica

Reporting the difference rows between two sources using Informatica

The purpose of the mappings discussed below are to report the difference rows between two sources in different scenarios.

Scenario 1: When there are difference rows in one of the sources and both the sources are either flat files or a flat file and a relational table.
To illustrate this scenario, the two sources considered are two comma separated flat files - EMPLOYEE_FILE_1.txt and EMPLOYEE_FILE_2.txt. The EMPLOYEE_FILE_2.txt has some extra records that need to be reported or loaded into the target which is a relational table having the same definition as the sources. The difference rows between the two flat files are highlighted in red as shown below.

Import the two flat file definitions using the Source Analyzer in Informatica PowerCenter Designer client tool as shown below.

Create a new mapping in the Mapping Designer and drag the two source definitions into the workspace.
Now, to identify the extra records from EMPLOYEE_FILE_2, a Joiner transformation followed by a Filter transformation is used. 

The illustration discussed below uses an unsorted Joiner Transformation and since both the sources are having few records, any one of the sources can be treated as the "Master" source. Here the EMPLOYEE_FILE_1 source is designated as the "Master" source. In practice, however, to improve performance for an unsorted joiner transformation, the source with fewer rows is treated as the "Master" source while for a sorted joiner transformation, the source with fewer duplicate key values is assigned as the "Master" source.

First drag all the ports from the source qualifier SQ_EMPLOYEE_FILE_2 into the joiner transformation. Notice the ports created in the joiner transformation are designated as the "Detail" source by default. Drag only the EMP_ID port from SQ_EMPLOYEE_FILE_1 into the joiner transformation as shown below.

Double-click the joiner transformation to open up the Edit view of the transformation. Click on the Condition tab and specify the condition shown below.

Since, we need to pass all the rows from the EMPLOYEE_FILE_2, the Join Type used is "Master Outer Join" in the Properties tab as shown below. This will ensure that all the rows from the "Detail" source and only the matching rows from the "Master" source will pass from the joiner transformation.

The Join types supported in the joiner transformation are described below.

The rows passed by the joiner transformation are shown below. For the missing rows in the "Master" source, the EMP_ID1 value is NULL.

Now, a filter transformation can be used ahead to pass only the records having EMP_ID1 as NULL since these rows correspond to the difference rows from EMPLOYEE_FILE_2 source. Pass all the rows from the joiner transformation into the filter transformation and add the Filter condition shown below in the Properties tab of the filter transformation.

The records passed by the filter transformation are shown below.

Link the EMP_ID, EMP_NAME and CITY ports to the target definition. The complete mapping is shown below.

Create a session task and a workflow. After running the workflow, the difference rows loaded into the target relational table are shown below.

Scenario 2: When there are difference rows in both the sources and both the sources are either flat files or a flat file and a relational table.
For this illustration, we consider both the sources are flat files. The difference rows in both the flat files are highlighted in red. The target relational table has an additional column SOURCE_NAME added, which indicates the source name where the difference row is present.

The EMPLOYEE_FILE_1 is treated as the "Master" source again for the unsorted joiner transformation. All the ports from the source qualifier SQ_EMPLOYEE_FILE_1 are passed to the joiner transformation as shown below because the difference records are present in both the sources.

The join condition for the joiner transformation is the same as the first scenario, but for this case, the Join Type is Full Outer Join as shown below.

The rows passed by the joiner transformation are shown below. For the missing rows in the "Master" source, the EMP_ID1, EMP_NAME1 and CITY1 values are NULL, while for the missing rows in the "Detail" source, the EMP_ID, EMP_NAME and CITY values are NULL.

The filter transformation shown below should only pass the rows having NULL values in EMP_ID or EMP_ID1 ports as these correspond to the difference rows in the EMPLOYEE_FILE_2 and EMPLOYEE_FILE_1 flat files respectively.

The filter condition used in the filter transformation is shown below.

The filter transformation passes the following rows.

Now, add an expression transformation after the filter transformation. Pass all the ports from the filter transformation to the expression transformation. The expression transformation should have the following ports in the order shown below.

The logic for the output port EMP_ID_OUT is that if the EMP_ID value from EMPLOYEE_FILE_2 source is NULL, pass the EMP_ID1 value from the EMPLOYEE_FILE_1 source, else return the EMP_ID value from EMPLOYEE_FILE_2 source. This logic works because for any row passed from the filter transformation, either the row from the "Master" source will have NULL values or the row from the "Detail" source will have NULL values. A similar logic is applied for the EMP_NAME_OUT and CITY_OUT output ports.

Another output port SOURCE_NAME_OUT is used to determine the source of the difference row. The expression used for this port is shown below.

The complete mapping is shown below.

Create a session task and a workflow. After running the workflow, the difference rows loaded into the target relational table are shown below.

Scenario 3: When there are difference rows in two relational tables residing in the same database.
For this illustration, two relational tables EMPLOYEE_TABLE_1 and EMPLOYEE_TABLE_2 having the same definition are considered. The rows present in both the tables are shown below and the difference rows are highlighted in red.

Import the table definition of any one source in the Source Analyzer. Here, the EMPLOYEE_TABLE_1 source definition is imported.

A simple SQL query that returns the difference rows in EMPLOYEE_TABLE_1 is given below.

SELECT EMP_ID, EMP_NAME, CITY FROM EMPLOYEE_TABLE_1 EMP_1
WHERE NOT EXISTS (SELECT EMP_ID FROM EMPLOYEE_TABLE_2 EMP_2
WHERE EMP_1.EMP_ID = EMP_2.EMP_ID) 

The above query can be modified to return the difference rows between the two source tables and also the table name where the difference row is present. The alias column 'SOURCE_NAME' gives the source table name of the difference row.
SELECT EMP_ID, EMP_NAME, CITY, 'EMPLOYEE_TABLE_1' AS SOURCE_NAME 
FROM EMPLOYEE_TABLE_1 EMP_1
WHERE NOT EXISTS (SELECT EMP_ID FROM EMPLOYEE_TABLE_2 EMP_2 
WHERE EMP_1.EMP_ID = EMP_2.EMP_ID) 
UNION
SELECT EMP_ID, EMP_NAME, CITY, 'EMPLOYEE_TABLE_2' AS SOURCE_NAME 
FROM EMPLOYEE_TABLE_2 EMP_2
WHERE NOT EXISTS (SELECT EMP_ID FROM EMPLOYEE_TABLE_1 EMP_1
WHERE EMP_1.EMP_ID = EMP_2.EMP_ID)

Add the above query to the source qualifier transformation in the Sql Query attribute value as shown below. This will override the default SQL query issued when the session runs. Ensure that the order of the columns in the SQL query match the order of the ports in the Source Qualifier.

The alias column 'SOURCE_NAME' value also needs to be passed to the target. For this purpose, a new port SOURCE_NAME is created in the source qualifier as shown below.

By default, all ports that are in the source qualifier are input/output ports. Hence, the new port SOURCE_NAME that is created should be linked to a field from the source definition having the same datatype i.e. the datatype varchar2 in the source definition changes to string in the source qualifier. If the port SOURCE_NAME is not linked, the session will fail with an error - TE_7020    Internal error. The Source Qualifier [SQ_EMPLOYEE_TABLE_1] contains an unbound field [SOURCE_NAME].

Link the CITY port from the source definition to the SOURCE_NAME port in the source qualifier. As a good practice, use an expression transformation in between the source qualifier and the target definition as shown below.

Create a session task and a workflow. After running the workflow, the difference rows loaded into the target relational table are shown below.