Greetings, I have a complex financial dataset that has three, or even more, robust examples. These examples are shown below in the Have and Want datasets. Please note that for the last example, ID 32145 which shifts to 32145DF once in default, the amount owed is reduced from $300 to $200 because the case was forwarded to default, where it reopens with a) a new ID that ends with DF and b) reduced the principal to the previous principal minus any collections to date. Thus, there should be data for only 32145CN and not both 32145 and 32145DF. The rationale is that it is the same ID/Customer and should not have the ID twice to double count the principal amounts. Have: ID Date Status Collection date Amount owed ever collected 1234 29-Feb-16 Open . 82.44 0 1234 31-Mar-16 Open . 82.44 0 1234 30-Apr-16 Open . 82.44 0 1234 31-May-16 Open . 82.44 0 1234 30-Jun-16 Open . 82.44 0 1234 31-Jul-16 Closed 20-Jul-16 82.44 82.44 1234 31-May-18 reopened 20-Jul-16 82.44 82.44 564789 29-Feb-16 Open 02-Feb-16 100 10 564789 31-Mar-16 Open 02-Feb-16 100 10 564789 30-Apr-16 Open 04-Apr-16 100 30 564789 31-May-16 Open 15-May-16 100 40 564789 30-Jun-16 Open 12-Jun-16 100 70 564789 31-Jul-16 Open 12-Jun-16 100 70 564789 31-May-18 Open 22-May-18 100 100 32145 29-Feb-16 Open . 300 0 32145 31-Mar-16 Open . 300 55 32145 30-Apr-16 Open 15-Apr-16 300 100 32145 31-May-16 Open 15-Apr-16 300 100 32145DF 31-May-16 default 12-May-16 200 120 32145DF 30-Jun-16 default 06-Jun-16 200 130 Want: ID Date Status Collection date Amount owed ever collected this month amount 1234 29-Feb-16 Open . 82.44 0 0 1234 31-Mar-16 Open . 82.44 0 0 1234 30-Apr-16 Open . 82.44 0 0 1234 31-May-16 Open . 82.44 0 0 1234 30-Jun-16 Open . 82.44 0 0 1234 31-Jul-16 Closed 20-Jul-16 82.44 82.44 82.44 1234 31-May-18 reopened 20-Jul-16 82.44 82.44 0 564789 29-Feb-16 Open 02-Feb-16 100 10 10 564789 31-Mar-16 Open 02-Feb-16 100 10 0 564789 30-Apr-16 Open 04-Apr-16 100 30 20 564789 31-May-16 Open 15-May-16 100 40 10 564789 30-Jun-16 Open 12-Jun-16 100 70 30 564789 31-Jul-16 Open 12-Jun-16 100 70 0 564789 31-May-18 Open 22-May-18 100 100 30 32145CN 31-May-16 default 12-May-16 300 120 20 32145CN 30-Jun-16 default 06-Jun-16 300 130 10 Thank you
... View more