2 Replies Latest reply: Jan 24, 2012 9:34 AM by Jason Schmidtlein RSS

    Lookup value using expressions

      In the following straight table I would like the third column "EDC_LMT_HIGH to obtain the value for the latest "Begin_Date" that falls on or before a given effective date. Is this possible via an expression?

       

       

      table.PNG

      Here are the other tables.

       

      limits.PNG

       

      effective.PNG

        • Lookup value using expressions
          Stefan Wühl

          Jason,

           

          you could probably resolve your request using FirstSortedValue() and a set expression to limit the BEGIN_DATE values you want to look up.

           

          Something along

          =FirstSortedValue({<BEGIN_DATE = {"<=$(=max(EFFECTIVE_DATE))"} >} EDC_LMT_HIGH, -BEGIN_DATE)

           

          Please check out FirstSortedValue and set analysis in the Help file.

          This should work in a table with dimension NAME, above expression and if you want the BEGIN_DATE accordingly:

          = max({<BEGIN_DATE = {"<=$(=max(EFFECTIVE_DATE))"} >} BEGIN_DATE)

           

          Or maybe I misunderstood your setting, so some sample rows of your data, best coded as INLINE table or in a small sample app might help.

           

          Regards,

          Stefan