8 Replies Latest reply: Jul 27, 2017 12:05 PM by Tanya Saloom RSS

    Return Most Recent Obs

    Tanya Saloom

      Hi,

       

      I'm working with a synthetic data set in an EAV format, entity-attribute-value.  I'd like to return the most recent health measure name and value for each individual. Item level is the base table that I loaded in, I want to have a second table that just shows the most recent.

       

      Here's a sample:

         

      IDGenderagevaluehealthMeasureobsMonth
      1Female189ESS_score3
      1Female18108ANAM4
      1Female18NoBP_hypertensive9
      1Female18NoBP_hypertensive6
      1Female18YesappropriateScreen6
      1Female18YesappropriateScreen9
      1Female18GoodcardioTest10

       

       

      So for person with ID one, ideally I would have 5 rows, dropping the ones with BP hypertensive and month 6 and appropriate screen and month 6.  (This data set has 100s of people in it...)

       

      What I have hasn't been working:

       

      [MostRecentObs]:

      Load

      ID,

          ObsMonth,

          Gender,

           Age,

          healthMeasure,

          value

       

      Resident ItemLevel

      where ObsMonth= max(ObsMonth)

      Group by ID,

      Gender,

      "age",

      healthMeasure

       

      Any help would be appreciated!

       

      Best,

      Tanya