Panorama > Data Types > Arrays
 
arrayreverselookup(
arrayreverselookup(
ARRAY
,
KEY
,
MAINSEPARATOR
,
SUBSEPARATOR
,
DEFAULT
)

The arrayreverselookup( function uses a value to look up a key in a two dimensional text array containing key/value pairs (see Text Arrays).

Parameters

This function has five parameters:

array – is the text of the array. This array must be two-dimensional, which means that it must be organized into virtual rows and columns. Each row is separated from the next row by the main separator character (see below). In addition, each row is divided into two columns by the sub separator character. The first column contains the key, the second column contains the associated value.

key – is the value you are looking for. If this value matches a value in the first “column” of the table, the value in the second column of that row will be returned. If there is no match, the default value will be returned.

mainseparator – is the character that separates each row.

subseparator – is the character that separates the columns within each row.

default – is the value that should be returned if there is no match.

Description

This function uses a value to look up a key in a two dimensional text array (see Text Arrays). It is very similar to the arraylookup( function, but uses the value to lookup the key instead of the key to lookup the value.

If the main separater is a carriage return and the sub separator is a comma, you can easily see the two dimensional structure of the array.

global stateAbbreviations
stateAbbreviations="AL,Alabama
AK,Alaska
AZ,Arizona
AR,Arkansas
CA,California
...
WI,Wisconsin
WY,Wyoming"

Once you’ve set up an array like this, you can use it to look up keys.

arrayreverselookup(stateAbbreviations,"Colorado",cr(),",","-") ☞ CO
arrayreverselookup(stateAbbreviations,"Rhode Island",cr(),",","-") ☞ RI
arrayreverselookup(stateAbbreviations,"Alberta",cr(),",","-") ☞ -

In the final example, there is no state matching Alberta, so the default value, in this case -, is returned. You can set the default to anything you want, including nothing.

Using carriage returns for the main separator makes the organization of the array easy to see, but you can pick any separator characters you wish. Here is another way you could set up the array, using | and = as the separators.

global stateAbbreviations
stateAbbreviations="AL=Alabama|AK=Alaska|AZ=Arizona|AR=Arkansas| ... |WI=Wisconsin|WY=Wyoming"

This will work fine as long as you use the same separators in the arrayreverselookup( function.

arrayreverselookup(stateAbbreviations,"Oregon","|","=","Unknown") ☞ OR

The order of the rows in the array is unimportant unless there are duplicate key values. In that case, the the key associated with the first matching value in the array will be returned.

Note: This function is equivalent to:

array(array(default+subsep+mainsep+mainsep+thetable,1+arraysearch(thetable+mainsep,thekey+mainsep+"*",1,subsep),mainsep),1,subsep)

See Also

History

VersionStatusNotes
10.0No ChangeCarried over from Panorama 6.0.