Find the second day transaction amount for a Accnt_id.
Ex: Epected records are marked in red.
Acct_Hist
Accnt_id, Trans_Dt, Seq_Num
1001 23-10-09 1
1001 24-10-09 1
1001 25-10-09 1
1002 26-10-09 1
1002 27-10-09 1
1002 27-10-09 2
1002 28-10-09 1
Output:
Accnt_id, Trans_Dt ,Seq_Num
1001 24-10-09 1
1002 27-10-09 1
1002 27-10-09 2
How to find second Transaction date in a Table
Moderators: chulett, rschirm, roy
How to find second Transaction date in a Table
Last edited by arundwh on Fri Dec 04, 2009 12:47 am, edited 1 time in total.
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Assuming you are checking by the ids following with same date and incrementing sequence, you can
1.) Sort data on id and date with keyChange capture
2.) Set a stage variable - say svIncrNum - to zero on change of id
3.) if the next id is same with incrementNumber = svIncrNum + 1, then take it. else reset the 'previous' values.
1.) Sort data on id and date with keyChange capture
2.) Set a stage variable - say svIncrNum - to zero on change of id
3.) if the next id is same with incrementNumber = svIncrNum + 1, then take it. else reset the 'previous' values.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Sort by transaction date in ascending order, then use Remove Duplicates stage to preserve the last from each group. (I'm assuming a maximum of two rows per group.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.