This operator returns the value in a table that corresponds to a set of lookup values. If no match is found, it returns 0. In a data default expression, #GETTABVAL can also be used to return all the values in a column. #GETTABVAL is available in data default expressions, salary and service transformation expressions, Benefit Definitions, Accrual Definitions, and SubFormulas.

Syntax:

a #GETTABVAL b

where:

a is the name of the CSV or space delimited TXT file containing the table, in quotes (e.g., "RegMaxComp.txt"), or it's a scalar value created by assignment (e.g., a:= "RegMaxComp.txt"). The name does not include a path, and the file extension is optional.  If the extension is missing, ProAdmin looks for .CSV and then .TXT files in each of the standard search directories.  If the extension is specified, ProAdmin looks for the exact file name in each of the standard search directories.

Note: You cannot create scalar character strings (e.g., a:= "RegMaxComp.txt") in Benefit Formulas, subformulas, or basis formulas because (internally) ProAdmin automatically creates an array with a calculation dates number of identical values.

b is a parenthesized list of temporary variables (created by temporary assignment) that are used to look up a value in the table specified by a. In Benefit Definitions, Accrual Definitions and Subformulas, b may include components.

In a data default expression, if you append a ? to the file name (e.g., 'RegMaxComp.txt?'), then b is the index of the column you want returned. For example, 'RegMaxComp.txt?' #GETTABVAL (1) returns all the values in the first column found in the file RegMaxComp.txt. If you ask for a column that doesn't exist (e.g., 'RegMaxComp.txt?' #GETTABVAL (9999)), then zeroes are returned (one zero for each row in the table).

If the set of lookup values (b) corresponds to a value in the specified table (a), then that value is returned; otherwise, 0 is returned.

The GETTABVAL file containing the values and their associated lookup values must have the following characteristics:

• The file must be in one of these directories (and this is the search order):

1. Client directory
2. Regulatory files (per RegPath setting)
3. User directory
4. System directory (i.e., directory where ProAdmin is installed)
5. Historical interest rate directory HIRTDIR (as specified in the proadmin.ini file; only used if HIRTCODE=2)

For ProAdmin Server (and Calculator Testing), the Client and User directories are not defined (ignored), and this is the search order:​

1. Regulatory files (per RegPath setting) 
2. System directory (i.e., directory where ProAdmin is installed)
3. Historical interest rate directory HIRTDIR (as specified in the proadmin.ini file; only used if HIRTCODE=2)

• The 1^st row may contain descriptors (e.g., Date, Plan, Location, Rate) 
• The 1^st or 2^nd row may contain match indicators which define the type of match for a column:

1. E = exact match (e.g., Location or Plan)
2. R = range match (e.g., Effective Date). If the date lookup range is 1/1/2000 to 12/31/2000, the indicator for this range is 1/1/2000. If a salary lookup range for a value is from $20,000 up to but not including $25,000, the indicator for the $20,000 value is 20,000 and the indicator for the $25,000 value is 25,000.
3. I = ignore this column. Since #GETTABVAL only works with dates and numbers, you might, for example, want to include a column with the location name next to the column with the location code. Use an “I” to tell #GETTABVAL to ignore the column with the descriptor.
4. V= value. This column contains the values you want #GETTABVAL to retrieve.

• If there is no match indicator row, the first column is assumed to contain a range (R), any intermediate columns are assumed to contain exact matches (E), and the last column is assumed to contain the values (V). 
• There can only be one value (V) column. 
• After the ignored columns are dropped, any blank lines and rows containing characters are dropped/ignored. This allows you to include comments in the file. 
• Each row must contain exactly the same number of values (after ignored columns, blank rows, and rows containing characters are dropped).

Now, all the values in the table are numbers, ProAdmin codes (which are numbers), and dates.

• Numbers and dates are assumed to be formatted in the Windows local setting format. 
• The order of the table columns (after ignored columns, blank rows, and rows containing characters are dropped) must match the order of the list of temporary variables that make up the right argument to #GETTABVAL.
• When looking up values, exact matches are performed first, and then the range values are used.

Some additional pointers when using #GETTABVAL are:

• If #GETTABVAL tries to load a CSV file that happens to be open in Excel, you’ll get a File busy (being used by another application or user) message. This is because Excel “locks” a CSV file without allowing read rights. However, if the file was open in NotePad, you won’t get this message because NotePad only locks the file when you save it. Thus, opening the file in NotePad will facilitate results checking. 
• In detailed results , when you redisplay salary & service transformation expressions, Benefit Definitions, Accrual Definitions, or SubFormulas details for a saved calculation that used #GETTABVAL, the current version of the file is used, not the version that was available when the original calculation was performed. If the old/new timestamps are different, a warning message will be displayed in the table footnotes.
• When you redisplay the final results of a Benefit Definition, Accrual Definition, or SubFormulas (not the details of that calculation) in detailed results , the #GETTABVAL footnote shows the names, directories, and timestamps of all #GETTABVAL tables used in the original calculation (not the re-display timestamps).

 

Some additional #GETTABVAL topics: