11 Replies Latest reply: Jan 15, 2018 3:49 PM by Melissa Pluke RSS

    Finding a date range within a script

    Melissa Pluke

      Hi all,

      I am trying to accomplish the below issue within the load script. I am able to doing this using the FirstSortedValue function within a chart in the UI, but am not able to figure it out within a script.  I have the following situation:

      Table A:

      Key          DateStart          DateEnd          Rate

      ABCD     1/1/2016            1/31/2016        $10

      ABCD     1/1/2017            1/31/2017        $8

      WXYZ     1/1/2016            1/31/2016        $11

      WXYZ     1/1/2017            1/31/2017        $12

       

      Table B:

      ID          Key     Date

      1          ABCD     7/15/2016

      2          ABCD     12/30/2017

      3          ABCD     1/1/2018

      4          WXYZ     2/2/2016

       

      I need return the appropriate rate for each ID.

      This is the table I am expecting.

       

      Table B:

      ID          Key     Date                    Rate

      1          ABCD     7/15/2016          $10

      2          ABCD     12/30/2017        $8

      3          ABCD     1/1/2018              -

      4          WXYZ     2/2/2016          $11

       

      I've tried using applymap, but because of the multiple date range, I can't seem to figure it out. Any help would be appreciated. Thank you!