Hi all,
Apologies if this question has already been asked but I couldn't find one that matched what I'm trying to achieve.
I basically have list of IDs and with these IDs I need to check several conditions and create a new column based on that.
Here's an example:
ID | Date |
ABC | 1/1/2020 |
ABC | |
ABC | 1/1/2020 |
ABC | 1/1/2020 |
ABC | 1/1/2020 |
ABC | 1/1/2020 |
DEF | 1/1/2023 |
DEF | 1/1/2023 |
DEF | 11/11/2023 |
XYZY | 1/1/2020 |
XYZY | 11/11/2023 |
XYZY | 11/11/2023 |
XYZY | 11/11/2023 |
I have several IDs with different dates.
I want to check if each of the IDs have any null/blank value in the Date field and if yes, I want the new column to say "Null
If there's any dates that are in the future (e.g., the 2023 fields) then I want the new column to say "Future"
And if all dates are in the past then I would like it to say "past"
If there's a mixture of Null, Past, Future then it should be Null, if Past and Future then it should be past
Here's the expected result:
ID | Date | New Field-Check |
ABC | 1/1/2020 | Null |
ABC | Null | |
ABC | 1/1/2020 | Null |
ABC | 1/1/2020 | Null |
ABC | 1/1/2020 | Null |
ABC | 1/1/2020 | Null |
DEF | 1/1/2023 | Future |
DEF | 1/1/2023 | Future |
DEF | 11/11/2023 | Future |
XYZY | 1/1/2020 | Past |
XYZY | 11/11/2023 | Past |
XYZY | 11/11/2023 | Past |
XYZY | 11/11/2023 | Past |
I appreciate any help I could receive,
thank you!
Solved! Go to Solution.
Hi,@hal_dal
for your information.
Input | Output | |||
ID | Date | ID | Date | Check |
ABC | 1/1/2020 | ABC | 1/1/2020 | |
ABC | ABC | |||
ABC | 1/1/2020 | ABC | 1/1/2020 | |
ABC | 1/1/2020 | ABC | 1/1/2020 | |
ABC | 1/1/2020 | ABC | 1/1/2020 | |
ABC | 1/1/2020 | ABC | 1/1/2020 | |
DEF | 1/1/2023 | DEF | 1/1/2023 | Future |
DEF | 1/1/2023 | DEF | 1/1/2023 | Future |
DEF | 11/11/2023 | DEF | 11/11/2023 | Future |
XYZY | 1/1/2020 | XYZY | 1/1/2020 | Past |
XYZY | 11/11/2023 | XYZY | 11/11/2023 | Past |
XYZY | 11/11/2023 | XYZY | 11/11/2023 | Past |
XYZY | 11/11/2023 | XYZY | 11/11/2023 | Past |
Hi,@hal_dal
for your ref.
Input | Output | |||
ID | Date | ID | Date | Check |
ABC | 1/1/2020 | ABC | 1/1/2020 | |
ABC | ABC | |||
ABC | 1/1/2020 | ABC | 1/1/2020 | |
ABC | 1/1/2020 | ABC | 1/1/2020 | |
ABC | 1/1/2020 | ABC | 1/1/2020 | |
ABC | 1/1/2020 | ABC | 1/1/2020 | |
DEF | 1/1/2023 | DEF | 1/1/2023 | Future |
DEF | 1/1/2023 | DEF | 1/1/2023 | Future |
DEF | 11/11/2023 | DEF | 11/11/2023 | Future |
XYZY | 1/1/2020 | XYZY | 1/1/2020 | Past |
XYZY | 11/11/2023 | XYZY | 11/11/2023 | Past |
XYZY | 11/11/2023 | XYZY | 11/11/2023 | Past |
XYZY | 11/11/2023 | XYZY | 11/11/2023 | Past |
Had a look through as it looks a lot simpler then mine :), not sure if it captures the logic of a ID having a "mixture of Null, Past, Future" then it should be Null?
Thank you so much everyone for your help!
All answers definitely helped me get to where I needed to be for my final solution.
For the purpose of the example, I didnt provide an exact condition I needed but I definitely managed to alter the answers provided and make it work with what I need.
Thank you again!
No worries, awesome you get it working @hal_dal !