How to use the lookup function to subset data?

How to use the lookup function to subset data?

book

Article ID: KB0074173

calendar_today

Updated On:

Products Versions
Spotfire Statistica 13.1 and higher

Description

This article describes how to use the "lookup" function to subset in Statistica.
 

Environment

Windows

Resolution

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

The lookup function looks for a value in the first column of a table or range you specify, and return the corresponding value from a column you specify. The lookup table/range must be sorted by the key column.

Click here for more details on the lookup function
 

Example, "Batch Date">=Lookup("A1234","R1C2:R"+ToString(NCases)+"C6",4)

  • "Batch Date": The name of the column with the values to match against lookup variable.
  • "A1234“: The value or column/Variable to lookup. This is considered to be the key-value of the lookup
  • "R1C2:R"+ToString(NCases)+"C6": The range within which to search. In this case, all rows in columns 2 through 6. Note that this can also refer to another Statistica spreadsheet (stored in disk or Enterprise or a node in the workspace context)
  • "4": The column index within the specified range containing the value to compare. In this case, it's the fourth column of the specified range (column 2 through 6), which is the fifth column in the dataset. This value is relative to the key-column index. 

Using this formula as the inclusion criteria in a subset node will return all rows in the input data in which the value in ˜Batch Date" is later than or equal to the value found in the fourth column of the specified range where the value "A1234" occurs within this range for the first time.

Issue/Introduction

How to use the lookup function to subset data?
Powered by Wolken Software