Hello,
I need a help. For example I have the attached Data set :
| ID | Date_1 | Date_2 | Days Difference |
| 11111 | 10/11/2019 | 10/10/2019 | -1 |
| 11111 | 10/11/2019 | 10/13/2019 | 2 |
| 11111 | 10/11/2019 | 10/16/2019 | 5 |
| 44444 | 7/1/2019 | 6/26/2019 | -5 |
| 44444 | 7/1/2019 | 7/3/2019 | 2 |
| 44444 | 7/1/2019 | 6/29/2019 | -2 |
| 55555 | 12/16/2019 | 12/13/2019 | -3 |
| 55555 | 6/24/2019 | 7/1/2019 | 7 |
| 55555 | 12/27/2019 | 12/30/2019 | 3 |
| 22222 | 7/10/2019 | 7/8/2019 | -2 |
| 22222 | 12/27/2019 | 12/30/2019 | 3 |
| 22222 | 12/27/2019 | 12/28/2019 | 1 |
- I want to filter this data set based on the nearest 'Date_2' compared to 'Date_1'.
- For example, for ID 11111, 'Date_2' (10/10/2019) is the nearest to the 'Date_1' (10/11/2019). In another word I need to take the 'Days Difference' which is nearest to zero (0), it may negative difference or positive difference.
- Now there is another condition. For example for ID 44444, the closest 'Date_2' is either 7/3/2019 or 6/29/2019 compared to 'Date_1' (7/1/2019). The 'Days Difference' is same for both the days. In this case I need to take the positive nearest difference date that is 7/3/2019.
The final output will be like this :
| ID | Date_1 | Date_2 | Days Difference |
| 11111 | 10/11/2019 | 10/10/2019 | -1 |
| 44444 | 7/1/2019 | 7/3/2019 | 2 |
| 55555 | 12/27/2019 | 12/30/2019 | 3 |
| 22222 | 12/27/2019 | 12/28/2019 | 1 |
It will be a great help if anyone can resolve this query.
Regards,
Gaurab
