Pick Date based on status

[apthansh]

Hello - I have an example as below.

ID	date	Type
1	7/8/2020	Plan
1	7/9/2020	Done
1	7/10/2020	Close
2	7/9/2020	Plan
2	7/10/2020	Future
2	7/11/2020	Close

Here I want to show 2 Dates (Now & Next)  for each ID.If there is Type called Done for each ID, (Ex ID 1)for NOW I want to show 7/9/2020 and Next would be 7/10/2020 and if there is no Done (Ex: ID 2)  then Now would be 7/9/2020 and Next would be 7/11/2020.

Thank you much🙏

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD * INLINE [
    ID, date, Type
    1, 7/8/2020, Plan
    1, 7/9/2020, Done
    1, 7/10/2020, Close
    2, 7/9/2020, Plan
    2, 7/10/2020, Future
    2, 7/11/2020, Close
];

Left Join(tab1)
LOAD ID, If(Index(Concat(DISTINCT Type),'Done'), 'Y', 'N') As Flag,
		 FirstSortedValue(date,date) As StDt, FirstSortedValue(date,-date) As EdDt
Resident tab1
Group By ID;

Left Join(tab1)
LOAD *, If(Flag='Y' And Type='Done', 'Now', 
		  If(Flag='Y' And Peek(Type)='Done', 'Next',
			If(Flag='N' And date=StDt,'Now',
			  If(Flag='N' And date=EdDt,'Next')
			)
		  )	
		 ) As Status
Resident tab1;

[Saravanan_Desingh]

Output.

[apthansh]

Thank you Saran - That helped..I used the below expression in the front end..

If(Index(Concat(DISTINCT Status),'Done'),

FirstSortedValue({$<Type={'Done'}>} Date, -Date),

If(not Index(Concat(DISTINCT Status),'Done'),

FirstSortedValue({$<Type-={'Done'}>} Date, Date)

)
)