Give 1 record after aggregation

[sai_12]

Hi all ,hope you are all well

I have a business problem similar to this 

I have a table with the data and i want to get the only records with minimum date 

client	date	type
a	1/10/2022	cat
a	2/10/2022	dog
a	3/10/2022	cat
a	4/10/2022	dog
a	5/10/2022	lion
b	6/10/2022	cat
b	7/10/2022	dog
b	8/10/2022	cat
b	9/10/2022	dog
b	10/10/2022	lion

Expected output:

a	1/10/2022	cat
b	6/10/2022	cat

My aggragtion function to get the mininum date is aggr(min(date),client), but when i am adding type to the table it gives other records.

Looking for solution in front end

[Aasir]

If you want it from the front end try below expression in your table
=aggr(If(date = min(total <client> date), type), client, date, type)

[sidhiq91]

@sai_12  Please use the script below to get the desired output.

NoConcatenate
Temp:
Load client,
Date(date#(date,'DD/MM/YYYY'),'MM/DD/YYYY') as date,
type
Inline [
client, date , type
a, 1/10/2022, cat
a, 2/10/2022, dog
a, 3/10/2022, cat
a, 4/10/2022, dog
a, 5/10/2022, lion
b, 6/10/2022, cat
b, 7/10/2022, dog
b, 8/10/2022, cat
b, 9/10/2022, dog
b, 10/10/2022, lion
];

NoConcatenate
Temp1:
Load client,
date(min(date),'MM/DD/YYYY') as min_date
Resident Temp
group by client;

NoConcatenate
Final:
Load client&min_date as Key
Resident Temp1;
Inner join (Final)
Load client,type,date,
client&date as Key
Resident Temp;
Drop Field Key from Final;

Drop table Temp,Temp1;

Exit Script;

If this resolves your issue, please like and accept it as a solution.

[Ahidhar]

you can use this for front end  and uncheck include null values 

aggr(If(date = min(total <client> date), type), client, date, type) 

or you use this in load script

tab:
load * Inline
[
client,date,type
a,1/10/2022,cat
a,2/10/2022,dog
a,3/10/2022,cat
a,4/10/2022,dog
a,5/10/2022,lion
b,6/10/2022,cat
b,7/10/2022,dog
b,8/10/2022,cat
b,9/10/2022,dog
b,10/10/2022,lion
];

Inner Join(tab)
 tab1:
 load
 client,
 min(date) as date
 resident tab
 group by client;