1 Reply Latest reply: Mar 29, 2018 2:12 PM by Sunny Talwar RSS

    Load only Latest Date

    Erica Trotter

      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!

        • Re: Load only Latest Date
          Sunny Talwar

          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;