I have a data set. I have created column Flag for identifying difference days whereever is 30. Now I have to create a column where 1 row immediately before flag 1 should be -1 and flag 1 should be 0.
A | B | C | Date | DifferenceDays | Flag |
a | p | N | 5/31/2017 | [Null] | [Null] |
a | q | N | 5/31/2020 | 1096 | [Null] |
a | r | Y | 6/30/2020 | 30 | 1 |
a | s | Y | 3/31/2022 | 639 | [Null] |
b | t | N | 10/31/2020 | [Null] | [Null] |
b | u | Y | 11/30/2020 | 30 | 1 |
Sample output
A | B | C | Date | DifferenceDays | Flag | Output |
a | p | N | 5/31/2017 | [Null] | [Null] | |
a | q | N | 5/31/2020 | 1096 | [Null] | -1 |
a | r | Y | 6/30/2020 | 30 | 1 | 0 |
a | s | Y | 3/31/2022 | 639 | [Null] | |
b | t | N | 10/31/2020 | [Null] | [Null] | -1 |
b | u | Y | 11/30/2020 | 30 | 1 | 0 |
Solved! Go to Solution.