Load rows based on max date time

[Ciara]

I have a sql table that looks like below.

ProcedureID	ScheduleDate	Charge
1030	2019-01-11	860.46
1030	2019-12-31	860.46
1030	2020-01-01	887.05
1141	2018-01-01	4007.16
1141	2019-12-31	4176.26
1141	2020-01-01	4305.31
1419	2019-01-11	7657.88
1419	2020-01-01	7658.10

I'm trying to extract the data for Charge based on the Max(ScheduleDate) of each ProcedureID so what I save in my qvd file looks like below.

ProcedureID	ScheduleDate	Charge
1030	2020-01-01	887.05
1141	2020-01-01	4305.31
1419	2020-01-01	7658.10

Any help would be appreciated.

Thanks

Ciara

[tresesco]

Dim: ProcedureID

Exp1: Date(Max(Date))

Exp2: FirstSortedValue(Charge, -[ScheduleDate])

[Taoufiq_Zarra · Accepted solution]

One solution in load :

Input:

LOAD * INLINE [
    ProcedureID, ScheduleDate, Charge
    1030, 2019-01-11, 860.46
    1030, 2019-12-31, 860.46
    1030, 2020-01-01, 887.05
    1141 , 2018-01-01, 4007.16
    1141 , 2019-12-31, 4176.26
    1141 , 2020-01-01, 4305.31
    1419, 2019-01-11, 7657.88
    1419, 2020-01-01, 7658.10
];

Final:
noconcatenate


load ProcedureID, Date(Max(ScheduleDate),'YYYY-MM-DD') as ScheduleDate, FirstSortedValue(Charge,-ScheduleDate) as Charge resident Input group by ProcedureID;

drop table Input;

output :

[Ciara]

Thank you SO much Taoufiq!  The FirstSortedValue was where I was getting stuck!

This worked beautifully.  Thanks for such a quick response also.

Ciara