Hi all,
I have a dataset with 5 variables: Date, ID, Account, Closed, Value and I am wanting to create a new variable: New_Value.
Basically New_Value is just the Value of the Account except if the Account for that specific ID has a Closed indicator of 'Y' AND is the latest recorded instance for that Account based on the Date, then I would like to change the New_Value for this row to 0.
For Example, New_Value should look like this:
Date ID Account Closed Value New_Value
01/01/2019 1 1 Y $10 $0
25/12/2018 1 1 Y $72 $72
22/11/2018 1 1 Y $90 $90
20/11/2017 1 1 Y $87 $87
There are many accounts per ID with each having multiple dates.
Many thanks in advance