How to check the condition of multiple rows grouped by an ID

[hal_dal]

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!

[IraWatt · Accepted solution]

hey @hal_dal 

I've attached an example workflow which does this, I think I captured your logic.

[flying008]

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

[flying008]

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

[binuacs]

@hal_dal Another way of doing this

[IraWatt]

@binuacs,

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?

[hal_dal]

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!

[IraWatt]

No worries, awesome you get it working @hal_dal !