Hi,
I have below dataset. Column C3_flag is mark which I have to use and generate 2 columns as shown in output.
I have to create numbering as per the ascending order of date for each item in C1 considering C3_flag column. I want column C4 and Column C5 as shown in expected output.
Available dataset:
| C1 | C2_date | C3_flag |
| a | 4/6/2019 | |
| a | 9/10/2020 | |
| a | 22/11/2020 | 1 |
| a | 20/1/2021 | |
| a | 28/2/2021 | |
| a | 4/8/2021 | |
| b | 18/9/2018 | |
| b | 22/1/2019 | 1 |
| b | 26/2/2019 | |
| b | 20/6/2019 | |
| c | 20/9/2017 | |
| c | 20/5/2018 | |
| c | 26/7/2018 | |
| c | 24/9/2018 | 1 |
| c | 23/11/2018 | |
| c | 11/1/2019 | |
| c | 18/3/2019 | |
| d | 20/4/2018 | |
| d | 21/7/2018 | 1 |
| d | 23/4/2019 | |
Expected Output :
| C1 | C2_date | C3_flag | C4 | C5 |
| a | 4/6/2019 | | -2 | |
| a | 9/10/2020 | | -1 | |
| a | 22/11/2020 | 1 | 1 | 1 |
| a | 20/1/2021 | | 2 | 2 |
| a | 28/2/2021 | | 3 | 3 |
| a | 4/8/2021 | | 4 | 4 |
| b | 18/9/2018 | | -1 | |
| b | 22/1/2019 | 1 | 1 | 1 |
| b | 26/2/2019 | | 2 | 2 |
| b | 20/6/2019 | | 3 | 3 |
| c | 20/9/2017 | | -3 | |
| c | 20/5/2018 | | -2 | |
| c | 26/7/2018 | | -1 | |
| c | 24/9/2018 | 1 | 1 | 1 |
| c | 23/11/2018 | | 2 | 2 |
| c | 11/1/2019 | | 3 | 3 |
| c | 18/3/2019 | | 4 | 4 |
| d | 20/4/2018 | | -1 | |
| d | 21/7/2018 | 1 | 1 | 1 |
| d | 23/4/2019 | | 2 | 2 |
