Getting top 5 entries based on end date

[Ineshsingh]

I need to show top 3 items based on their end dates

ID	Service	End Date
33855	XYZ	1-Jun-23
5400	YZB	27-Aug-23
28004	XSD	31-May-23
26146	ASA	12-Jul-23
14358	LKI	30-Jun-23
26054	BKJN	3-Jan-24
32883	YFK	12-Aug-25

It should display:

ID	Service	End Date
28004	XSD	31-May-23
33855	XYZ	1-Jun-23
14358	LKI	30-Jun-23

and Additional if I can display text saying  - "Service XSD going to expire on 31st May 2023"

[QFabian · Accepted solution]

Hi @Ineshsingh , i made this example, hopes works for you :

//Script  to format the field as date :

Aux:
Load * INLINE [
ID, Service, EndDate
33855, XYZ, 1-Jun-23
5400, YZB, 27-Aug-23
28004, XSD, 31-May-23
26146, ASA, 12-Jul-23
14358, LKI, 30-Jun-23
26054, BKJN, 3-Jan-24
32883, YFK, 12-Aug-25
];

Aux:
Load
ID,
Service,
date#(EndDate, 'dd-MMM-YY') as EndDate2
Resident Aux
order by
EndDate;

drop table Aux;

exit script;

//Then in a straight table :

[QFabian]

@Ineshsingh Excelent!, here is included the comment :

Aux:
Load * INLINE [
ID, Service, EndDate
33855, XYZ, 1-Jun-23
5400, YZB, 27-Aug-23
28004, XSD, 31-May-23
26146, ASA, 12-Jul-23
14358, LKI, 30-Jun-23
26054, BKJN, 3-Jan-24
32883, YFK, 12-Aug-25
];

Aux:
Load
ID,
Service,
date#(EndDate, 'dd-MMM-YY') as EndDate2,
'Service ' & Service & ' going to expire on ' & date#(EndDate, 'dddd-MMM-YYYY') as Comment
Resident Aux
order by
EndDate;

drop table Aux;

exit script;