Search code examples
logicinformatica-powercenter

previous record end date update in informatica using next record start date

I have a problem regarding update of end date of previous record using the next records start date. The problem is both the records coming in, in the same table load. Also there is no unique row identifier except the combination of all columns. Example: Source Table

HICN       FIRST_NAME       LAST_NAME        M_NAME      DOB(string)  START_DATE
X123        ABC                DEF             M      '  19600101       1/1/2013
Y456        ABC                DEF             M         19600101       2/2/2014
Now, (this is my business requirement, nothing I can do about it) In the target I have an extra column END DATE. This is the first load and I have to identify on the fly using a concatenated combination of First name, last name, etc etc that the 1st and the second record are the same and if(and only if) the HICN number changes for the member I have to update the end_date of the 1st record( i.e. record with HICN X123) with the Start_Date of the 2nd record( i.e. record with HICN Y456) so my target should look like:

HICN FIRST_NAME  LAST_NAME   M_NAME     DOB(string)    START_DATE      END DATE
X123  ABC           DEF        M      '  19600101       1/1/2013       2/2/2014
Y456  ABC           DEF        M         19600101       2/2/2014      12/31/1990

I have figured out how to update date cols and flag(which i did not mention above for active and inactive hicn for a member) for a second run but not sure how to do this if both the records come in the same batch. Any help would be greatly appreciated. Thanks

Solution

  • Try these options:

    1. Create a column in target table like ROW_ID with VARCHAR2(100)

    2. Create an Expression in Expression transformation with following -

      MD5(Col1||''||col2||''||...etc)

      '*' - is an separator to get precised output

      This MD5 function will generate an 32 bit mask which we will use it in next steps

    3. Have a Dynamic lookup on the target table

    Now, every time when you receive a new row it will get added to target as well as Dynamic lookup. Hence, if you try to Match the ROW_ID field with 2nd rows calculated ROW_ID, you can easily find out the duplicate record.

    MD5 is one of the best and fastest way to find dups using whole column list. DO NOT foget to create Dynamic Lookup otherwise you wont be able to find dups.

    Let me know if you need further info.