Comparing Current record with Previous record

Chapter-3

Introduction

In Informatica we can store the value of a field(s) of previous record and compare it with the current record using an Expression transformation.

This is quite helpful in order to identify the change in the data and take the actions accordingly. Let us understand how to compare current record with previous record with examples.

Consider the following as the Source data

ITEM
-----
a
b
c
a
c
d
c
e
d

Q1. Design a mapping to load the all the items with no duplicate records into one target and the items with duplicates into another target.

Expected Output:

Solution

1. Select the object with above data as source and pass the data to a sorter transformation.
2. Sort the data based on ITEM with the Sort Order as Ascending.
3. Pass the data from Sorter to an Aggregator transformation.
4. In aggregator, select ITEM as the Group By field. In the Aggregate section, create a new field Count and assign value as COUNT(ITEM)
   Count=COUNT(ITEM)
5. At this stage the output from the aggregator will be as below
   ITEM, COUNT
   a,2
   b,1
   c,3
   d,2
   e,1
6. Now take a Joiner transformation into the mapping canvas and pass the data from Sorter and Aggregator to it.
7. There will be Field Name conflicts as the field with name ITEM is passed from both sorter and aggregator. In order to resolve it, add a prefix to the sorter data as “SRT_”.
8. Select the Join Type as Normal and Join Condition as SRT_ITEM = ITEM
9. In the joiner transformation check the Sorted Input property, else the mapping will fail.
10. Pass the data from joiner to a Router transformation. Create two groups with conditions as below
    Group Name   Condition
    Unique             Count=’1’
    Duplicate         Count>’1’
11. Map the data from Unique group one target and Duplicate group to another target.

The final mapping will be as below.

If you are wondering where we have compared current record with previous record, we haven’t. This actually helps us understand our next example where we compare the current record with previous record.

Q2. Design a mapping to load the all the unique items into one target and the duplicates into other.

Expected Output:

Solution

1. Select the object with above mentioned data as source and pass the data to a sorter transformation.
2. Sort the data based on ITEM with the Sort Order as Ascending.
3. Pass the data from Sorter to an Expression transformation.
4. In the expression, create a variable field V_PrevItem and assign value as ITEM. This field is used to hold the item value of the previous record.
5. Create an another variable field V_count to assign the incremental values to each record. This value needs to be reset whenever the item changes.
   V_count = IIF(ITEM!=V_PrevItem,1,V_count+1)
6. Finally create an output field O_count and set the value as V_count.
7. The expression fields will be as below.
   V_count = IIF(ITEM!=V_PrevItem,1,V_count+1)
   V_PrevItem = ITEM
   O_count = V_count
   NOTE: The order of fields is important here because if we assign the value to the V_PrevItem before calculating V_count, then both ITEM and V_PrevItem will have same value which is not expected.
8. The output from the expression transformation will be as below
   ITEM, O_count
   a,1
   a,2
   b,1
   c,1
   c,2
   c,3
   d,1
   d,2
   e,1
9. Pass the data from joiner to a Router transformation. Create two groups with conditions as below
   Group Name   Condition
   Unique             O_count=’1’
   Duplicate         O_count>’1’
10. Map the data from Unique group to one target and Duplicate group to another target.

The final mapping will be as below.

Understand the difference between Q1 and Q2. In order to find the duplicates, we can use an aggregator and get the count of each Item. Then using a joiner, we assigned the count value to each record. But in order to assign an incremented value to each item individually we have to track the change in the item value. This can be achieved by comparing the current and previous record values.

Let us look at some more examples to understand the concept better.

Consider the following as the Source data

Q3. Design a mapping to calculate cumulative sum of salary of employees.

Expected Output:

Solution

1. Select the object with above mentioned data as source and pass the data to an expression transformation.
2. In the expression create a variable field V_CumSalary and assign value as V_CumSalary+Salary.
3. Create an output field Cum_Salary and assign value as V_CumSalary.
4. The fields in the expression will be as below.
   V_CumSalary = V_CumSalary+Salary
   Cum_Salary = V_CumSalary
5. Pass the data from expression to a target transformation and configure the target.

We have created a variable and started adding salary values to it for each record here.

Q4. Design a mapping to calculate cumulative sum of salary of employees Department wise.

Expected Output:

For the record 104 the cumulative salary value is set as salary value as the department got changed from 1111 to 2222.

Solution:

1. Select the object with above mentioned data as source and pass the data to a sorter transformation.
2. In sorter, sort the data on DEPT_ID and EMP_ID with sort order as ascending.
3. Pass the data from sorter to an expression transformation.
4. In the expression, create a variable field V_PrevDeptID and assign value as DEPT_ID. This field is used to hold the department id value of the previous record.
5. Create an another variable V_CumSalary and assign value as IIF(DEPT_ID!=V_PrevDeptID, SALARY,V_CumSalary+SALARY)
6. Finally create an output field Cum_Salary and assign value as V_CumSalary.
7. The expression fields will be as below.
   V_CumSalary = IIF(DEPT_ID!=V_PrevDeptID, SALARY,V_CumSalary+SALARY)
   Cum_Salary = V_CumSalary
   V_PrevDeptID = DEPT_ID
8. Pass the data from expression to a target transformation and configure the target.

Notice that whenever the department ID changes, we are setting the cumulative salary value as the salary value of the record else we are calculating the sum.

In Q3, the final record will have the total sum of the salary of employees. In Q4, the final record of each department will have the total sum of salary of the employees of that department.

Consider the following as the Source data

Q5. Design a mapping to concatenate the item values in a table

Expected Output:

Solution:

1. Select the object with above mentioned data as source and pass the data to a sorter transformation.
2. In sorter, sort the data on DEPT_ID and ITEM with sort order as ascending.
3. In the expression create a variable field V_Count and assign value as V_Count+1. This is to identify the first item.
4. Create an another variable V_ItemList and assign value as IIF(V_Count=1, ITEM, V_ ItemList ||’,’||ITEM)
5. Finally create an output field Item_List and assign value as V_ItemList.
6. The expression fields will be as below
   V_Count = V_Count+1
   V_ItemList = IIF(V_Count=1, ITEM, V_ ItemList ||’,’||ITEM)
   Item_List = V_ItemList
7. Pass the data from expression to a target transformation and configure the target.

So using the count variable we are identify the first record and anything after that is getting concatenated to the previous calculated value.

Q6. Design a mapping to concatenate the item values in a table department wise.

Expected Output:

Solution:

1. Select the object with above mentioned data as source and pass the data to a sorter transformation.
2. In sorter, sort the data on DEPT_ID and ITEM with sort order as ascending.
3. In the expression, create a variable field V_PrevDeptID and assign value as DEPT_ID. This field is used to hold the department id value of the previous record.
4. Create an another variable V_ItemList and assign value as IIF(DEPT_ID!=V_PrevDeptID, ITEM, V_ ItemList ||’,’||ITEM)
5. Finally create an output field Item_List and assign value as V_ItemList.
6. The expression fields will be as below.
   V_ItemList = IIF(DEPT_ID!=V_PrevDeptID, ITEM, V_ ItemList ||’,’||ITEM)
   Item_List = V_ItemList
   V_PrevDeptID = DEPT_ID
7. Pass the data from expression to a target transformation and configure the target.

Notice that whenever the department ID changes, we are setting the list value as the Item value of the record else we are concatenating the item values.

In Q5, the final record will have the total concatenated list of the item values. In Q6, the final record of each department will have the total concatenated list of items of that department.

Conclusion

In the examples discussed above we have compared records in Q2, Q4 and Q6 only.

But the other examples Q1, Q3 and Q5 scenarios can be easily converted into Q2, Q4 and Q6 and they help us understand the scenario and need to compare the current and previous record.

Compare current record with previous record whenever it is necessary to track the change in the data by storing the previous record value in a variable field in expression.