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!
