compare current record with previous & next record
Posted: Wed Nov 12, 2014 10:53 am
Hi All,
I need some guidance to develop a logic. This is comparison of current record with previous group and next group.
In the data below. I have Master data , and Trans Data ( currect day transactions).
Process_TS divides into groups. we have 6 groups in total.
The first group records will check in next group based on Bank_KEY + ACCT_ID. If present in next group you can ingore.
But if they are not present then, they wil be treated as deleted with giving the group process_ts into TERMINATION_TS.
if you take example of ACCT_ID = 2226472, it was present in all times, and not at last group, so
it master record is Deleted with that group time.
if you take example of ACCT_ID = 3336507, it was not present at 2014-11-06 11:32:17.104000, so it was deleted but again it got added
at 2014-11-06 18:18:18.184000 , so a new insert should be genereated, but again it was not present at 2014-11-06 20:20:20.202000, so a delete
record should be genereated with Termination_TS
like wise, we need to compare with previous group and next group. How to build in datastage 8.7 ?
I need some guidance to develop a logic. This is comparison of current record with previous group and next group.
In the data below. I have Master data , and Trans Data ( currect day transactions).
Process_TS divides into groups. we have 6 groups in total.
The first group records will check in next group based on Bank_KEY + ACCT_ID. If present in next group you can ingore.
But if they are not present then, they wil be treated as deleted with giving the group process_ts into TERMINATION_TS.
if you take example of ACCT_ID = 2226472, it was present in all times, and not at last group, so
it master record is Deleted with that group time.
if you take example of ACCT_ID = 3336507, it was not present at 2014-11-06 11:32:17.104000, so it was deleted but again it got added
at 2014-11-06 18:18:18.184000 , so a new insert should be genereated, but again it was not present at 2014-11-06 20:20:20.202000, so a delete
record should be genereated with Termination_TS
like wise, we need to compare with previous group and next group. How to build in datastage 8.7 ?
Code: Select all
ACCT_ID BANK_KEY PROCESS_TS ROLE SEQUENCE TERMINATION_TS
Master 2226472 30001928 2014-09-25 19:23:01.071000 O 1
Master 2226472 30001928 2014-09-25 19:23:01.071000 D 2
Master 3336507 30001928 2014-09-25 19:23:01.071000 O 2
Master 3336507 30001928 2014-09-25 19:23:01.071000 D 1
Master 4446510 30001928 2014-09-25 19:23:01.071000 D 1
Master 5557554 30001928 2014-09-25 19:23:01.071000 D 1
Transc 2226472 30001928 2014-11-06 11:32:17.104000 O 1
Transc 2226472 30001928 2014-11-06 11:32:17.104000 D 2
Transc 4446510 30001928 2014-11-06 11:32:17.104000 D 1
Transc 5557554 30001928 2014-11-06 11:32:17.104000 D 1
Transc 6662578 30001928 2014-11-06 11:32:17.104000 O 1
Transc 2226472 30001928 2014-11-06 16:22:41.572000 O 1
Transc 2226472 30001928 2014-11-06 16:22:41.572000 D 2
Transc 2226472 30001928 2014-11-06 18:18:18.184000 O 1
Transc 2226472 30001928 2014-11-06 18:18:18.184000 D 2
Transc 4446510 30001928 2014-11-06 18:18:18.184000 D 1
Transc 5557554 30001928 2014-11-06 18:18:18.184000 D 1
Transc 3336507 30001928 2014-11-06 18:18:18.184000 O 2
Transc 3336507 30001928 2014-11-06 18:18:18.184000 D 1
Transc 2226472 30001928 2014-11-06 19:19:19.194000 O 1
Transc 2226472 30001928 2014-11-06 19:19:19.194000 D 2
Transc 4446510 30001928 2014-11-06 19:19:19.194000 D 1
Transc 5557554 30001928 2014-11-06 19:19:19.194000 D 1
Transc 3336507 30001928 2014-11-06 19:19:19.194000 O 2
Transc 3336507 30001928 2014-11-06 19:19:19.194000 D 1
Transc 6662578 30001928 2014-11-06 19:19:19.194000 O 1
Transc 6662578 30001928 2014-11-06 20:20:20.202000 O 1
Desired Output --> as below
ACCT_ID BANK_KEY PROCESS_TS ROLE SEQUENCE TERMINATION_TS
Deleted 3336507 30001928 2014-09-25 19:23:01.071000 O 2 2014-11-06 11:32:17.104000
Deleted 3336507 30001928 2014-09-25 19:23:01.071000 D 1 2014-11-06 11:32:17.104000
Insertd 6662578 30001928 2014-11-06 11:32:17.104001 O 1
Deleted 4446510 30001928 2014-09-25 19:23:01.071000 D 1 2014-11-06 16:22:41.572000
Deleted 5557554 30001928 2014-09-25 19:23:01.071000 D 1 2014-11-06 16:22:41.572000
Deleted 6662578 30001928 2014-11-06 11:32:17.104001 O 1 2014-11-06 16:22:41.572000
Insertd 4446510 30001928 2014-11-06 18:18:18.184000 D 1
Insertd 5557554 30001928 2014-11-06 18:18:18.184000 D 1
Insertd 3336507 30001928 2014-11-06 18:18:18.184000 O 2
Insertd 3336507 30001928 2014-11-06 18:18:18.184000 D 1
Insertd 6662578 30001928 2014-11-06 19:19:19.194000 O 1
Deleted 4446510 30001928 2014-11-06 18:18:18.184000 D 1 2014-11-06 20:20:20.202000
Deleted 5557554 30001928 2014-11-06 18:18:18.184000 D 1 2014-11-06 20:20:20.202000
Deleted 3336507 30001928 2014-11-06 18:18:18.184000 O 2 2014-11-06 20:20:20.202000
Deleted 3336507 30001928 2014-11-06 18:18:18.184000 D 1 2014-11-06 20:20:20.202000
Deleted 2226472 30001928 2014-09-25 19:23:01.071000 O 1 2014-11-06 20:20:20.202000
Deleted 2226472 30001928 2014-09-25 19:23:01.071000 D 2 2014-11-06 20:20:20.202000