Load only Latest Date

[etrotter]

Hello,

I have this table:

Temp:

Load Distinct

Worker_ID,

Base_Rate,

Compensation_Effective_Date

Resident Compensation;

I would like to only show the Base Rate from the most recent Compensation_Effective_Date. I have tried with a where statement, a lastvalue functions, but I have yet to get either of them to work.

I'd appreciate if you could help, thanks in advance!

[sunny_talwar · Accepted solution]

Try this

Temp:

Load Distinct

     Worker_ID,

     Base_Rate,

     Compensation_Effective_Date

Resident Compensation;

Right Join (Temp)

LOAD Worker_ID,

     Max(Compensation_Effective_Date) as Compensation_Effective_Date

Resident Temp

Group By Worker_ID;