Range Order Matches
For this discussion, consider these two CSV files (in Excel):
Effdate Age Value R R V 1/1/1900 0 1.5 1/1/2000 0 1.7 1/1/2000 55 2.0 1/1/2000 60 3.0 1/1/2005 0 0.4 1/1/2005 55 0.5
Age Effdate Value R R V 0 1/1/1900 1.5 0 1/1/2000 1.7 55 1/1/2000 2.0 60 1/1/2000 3.0 0 1/1/2005 0.4 55 1/1/2005 0.5 The difference between these tables is that the Age column is now the first column (instead of the 2nd column). Looking at these tables, you might expect to always get the same value, but you would be wrong. Let’s show the Age first CSV file as #GETTABVAL uses it (the range columns are sorted in descending order):
Age Effdate Value R R V 0 1/1/1900 1.5 0 1/1/2000 1.7 0 1/1/2000 0.4 55 1/1/2000 2.0 55 1/1/2005 0.05 60 1/1/2000 3.0 If you try to look up the 12/31/99 value for someone born 3/17/1939 (age 60.8) using the 1st CSV file (where Effdate is the first column), #GETTABVAL returns 1.5. It looks for the appropriate row(s) for 12/31/99 and finds only one: (1/1/1900,0,1.5). Since age 60 is after age 0, 1.5 is returned
If you try to look up the age 60.8 value as of 12/31/99 using the 2nd CSV file (where Age is the first column), #GETTABVAL returns 0. It looks for the appropriate row(s) for age 60. 8 and finds only one: (60,1/1/2000,3.0). Since 12/31/99 is before 1/1/2000, there is NO value to lookup, and 0 is returned.