Hi Community,
Below is a sample set of my data. I'm trying to evaluate each ID and create a new column called "Clearance Status" that would evaluate whether the ID was previously cleared or if its the first clearance. If it was never cleared and the release date is blank, it should say "needs clearance"
Any idea how I can do this?
Input:
| ID | Entry Date | Release Date |
| 123 | 1/1/2022 | 1/2/2022 |
| 123 | 1/4/2022 | 1/5/2022 |
| 123 | 1/6/2022 | |
| 456 | 2/1/2022 | 2/2/2022 |
| 456 | 2/5/2022 | |
| 789 | 3/1/2022 | 3/5/2022 |
| 101112 | 4/1/2022 | |
Expected Output:
| ID | Entry Date | Release Date | Clearance Status |
| 123 | 1/1/2022 | 1/2/2022 | First clearance |
| 123 | 1/4/2022 | 1/5/2022 | Previously cleared |
| 123 | 1/6/2022 | | Previously cleared |
| 456 | 2/1/2022 | 2/2/2022 | First clearance |
| 456 | 2/5/2022 | | Previously cleared |
| 789 | 3/1/2022 | 3/5/2022 | First clearance |
| 101112 | 4/1/2022 | | Needs clearance |
