How to remove duplicate records using Expression

How to remove duplicate records using Expression

In the last post we have used Aggregator Transformation to remove the duplicate records. In this post we will use the Expression transformation to remove the duplicate JOB_ID from the source Flat file.

Source – Flat File
Target – Oracle Table
Key port – JOB_ID
Transformations 

Sorter – To sort the source records (To improve Aggregator Performance)
Expression – To compare the current and previous value and assign duplicate count
Filter – To Filter out the duplicate records

How to remove duplicate records using Aggregator
Source Flat File having duplicate JOB_ID
How to remove duplicate records using Expression Transformation
Complete Mapping with Sorter, Expression & Filter
How to remove duplicate records using Expression Transformation
Have a close look on the transformations and understand how we connected the ports.
How to remove duplicate records using Expression Transformation
Sorter Transformation – Here we are sorting the record using key JOB_ID.
How to remove duplicate records using Expression Transformation
Expression Transformation – To compare the current JOB_ID with the previous ID and increment a counter if both are matching, For those duplicate records the counter will be 1 or greater.

 

Here is the variable & output ports used in Expression.

V_COUNTER = IIF(V_PREV_JOB_ID=JOB_ID,V_COUNTER + 1)
O_COUNTER = V_COUNTER
V_PREV_JOB_ID = JOB_ID

How to remove duplicate records using Expression Transformation
Filter Transformation – Process only if the counter is Zero, it means there is no duplicate Job_ID. Everything else will be skipped.
How to remove duplicate records using Expression Transformation
Workflow Log – 4 duplicate records got removed from the source and everything else got loaded in to target.
How to remove duplicate records using Expression
Target Table – See the results now, We have only unique JOB_ID in the target table.

You May Also Like

Leave a Reply

Your email address will not be published. Required fields are marked *