How to use the Lookup function in Statistica?

How to use the Lookup function in Statistica?

book

Article ID: KB0074051

calendar_today

Updated On:

Products

Statistica

Description

The use case is to populate a variable based on a lookup Statistica spreadsheet. The concept is similar to looking up from a database table or Lookup function in Excel described here : https://support.office.com/en-us/article/LOOKUP-function-446d94af-663b-451d-8251-369d5e3864cb
 

Issue/Introduction

How to use the Lookup function in Statistica?

Resolution

The function looks for a value in the first column of a table or range you specify, and returns the corresponding value from a column you specify. The lookup is not case sensitive and trailing and leading spaces will not affect the search.

PRE-REQUISITE : The lookup table/range must be sorted by the key column.

SYNTAX: Lookup(key-value, table-ref, col-idx [, range])

PARAMETERS: There are four parameters for this function that are detailed below.

key-value: the value or variable reference you want to look up in the table. In Data Entry context, this can be a label name.
 
table-ref: is the lookup table source, such as a path to a spreadsheet or a range in the current spreadsheet (e.g. 'c:\path\spr.sta', 'R1C1:R10C2', 'c:\path\spr.sta?R1C1:R10C2', 'enterprise://path/spr.sta', etc) . In the context of Workspace, this can also be the name of a node that contains a spreadsheet. The range is optional if the key-value is the first column in the table being looked up. If the key value is not the first variable in the lookup table, specify the range.
 
col-idx: the column index of the data to return from the lookup table with column 1 being the key column

range: should be true or false (1 or 0) depending if this is a range lookup or exact match

EXAMPLE:
Let us walk through an example. Consider we have to lookup State based on City in the below spreadsheet.  

The lookup table is named StateLookup and added to Enterprise location : Statistica Enterprise/Lookup/

 

Step 1:Sort the lookup table on the key-value. In this scenario, we are looking up based on variable "City".

 
Step 2: Type the Lookup function for the State Variable  =Lookup("City","Enterprise://Lookup/StateLookup.sta",2,0). To expand on the parameters - "City" is our key-value based on which we are looking up. The second parameter is the location of the lookup spreadsheet. It points to Enterprise since it is saved in Enterprise Manager. This could be a disk drive location or the name of a node in the context of a workspace . Since "City" is the first column being looked up, there is no need to specify a range. Hence the fourth parameter is 0 (Not a range lookup) . The column index that we are returning (relative to the key column being the first) is 2, so we enter 2.

Click OK.

 

Step 3 : Voila !


Note : If the Lookup table is in a database, workspaces with data configurations can be used and the reference to the table can be the name of a node in the same workspace.




The function can also be used in the context of Data Entry as calculated formula of a label.

  User-added image