I am reading data from a csv file which does not have a primary key and there are duplicate entries as well. This data is loaded into database. The requirement is to implement the soft delete when something gets deleted from source.
For example if there are 4 occurrences of a particular record in source and once of them is deleted, one of the record to be marked as N in target during the next load and rest three would be Y. In case two of them gets deleted, two records should be marked as N and rest two will remain as Y.
I am thinking about sequencing the group of duplicates in incremental order and using that column to differentiate between the duplicates. However I'm not sure how to practically implement this.
Select all active records from Target and source and store them in buffer (tHashoutput) component.
While fetching records from Source create a Hash value (Hash1) of all the applicable columns using DataMasking.createMD5 or any similar hashing technique in tMap1. In tMap2 create a numeric sequence for Hash value using Numeric.sequence(Hash1,1,1). Use this Sequence as ID for the Target Table and create another Hash key (Hash2) using all the applicable columns +Hash1 + Numeric sequence (ID)
Insert New Record: Use src data as input1 and target data as input 2 into tMap and join them on Hash2 and ID created in previous step and all the applicable columns. Insert if ID == null . Add below columns with default values as mentioned
4. Logical delete of records deleted in source: Use target as input 1 and source as input 2 into tmap and join them on Hash2 and ID created in step 2 and all the applicable columns. If ID == null update the active indicator to N.
You may omit the tSortRow component if the duplicates rows are exactly the same, if there is someting different you can order by these fields. Then in the tMap create a variable with integer type. In the expression write: Numeric.sequence(fields you want to group by,1,1). These fields must appear as only one string field so you can do something like: Numeric.sequence(field1.toString() + field2.toString(),1,1)
Since the source doesn't have a primary key and has duplicated rows, Marking any of the deleted record as inactive in target is looking like a challenge.
Select all active records from Target and source and store them in buffer (tHashoutput) component.
While fetching records from Source create a Hash value (Hash1) of all the applicable columns using DataMasking.createMD5 or any similar hashing technique in tMap1. In tMap2 create a numeric sequence for Hash value using Numeric.sequence(Hash1,1,1). Use this Sequence as ID for the Target Table and create another Hash key (Hash2) using all the applicable columns +Hash1 + Numeric sequence (ID)
Insert New Record: Use src data as input1 and target data as input 2 into tMap and join them on Hash2 and ID created in previous step and all the applicable columns. Insert if ID == null . Add below columns with default values as mentioned
4. Logical delete of records deleted in source: Use target as input 1 and source as input 2 into tmap and join them on Hash2 and ID created in step 2 and all the applicable columns. If ID == null update the active indicator to N.
