Skip to main content

SELECT (+videos)

Updated

The SELECT function allows you to select a subset of data based on a specified criteria.

The syntax for SELECT is:

SELECT (data, condition)

data an array of data.
condition the criteria to select from the data. This parameter must return an array of true and false values. Note: it is not necessary to use the IF function in the condition parameter.

The data and condition parameters must be the same number of values because each item in the condition array is applied to each item at the same position in the data array.

Learn more about SELECT:

Introduction to SELECT

Use SELECT with conditions

Use SELECT with a User Input Control

Using SELECT

The following examples use the data source below.

  A B C
1 Fruit Units Date
2 Apples 10 9-Nov-15
3 Oranges 15 9-Jul-15
4 Pears 20 9-Sep-15

 

In this example, we use a greater than or equal to condition to return specific values from column B (B:B).

SELECT(data, condition)

  • data: set to the Fruit data in column A (A:A).
  • condition: set to the Units data in column B (B:B), and set greater than or equal to a specific value from the Units column, 15.

SELECT(A:A,(B:B>=15))

Result: Oranges, Pears

SELECT and variables

These examples show you how to use SELECT with a variable created in a User Input Control component.

SELECT returns the value from column B (B:B) that matches the fruit (A:A) selected from the drop-down list.  If Apples is selected from the drop-down list, the value returned is 10.

SELECT(data, ($variable=values))

  • data: set to Units in column B (B:B).
  • $variable: set to your unique variable.
  • values: set to Fruit in column A (A:A).

Hover over the image below to watch a short video.

 

SELECT and OR

In this example, SELECT uses the OR function to add an "All" option to the drop-down list. This formula will return all values if All is selected from the drop-down list.

You can also add the All option by checking the Include the "All" data option on the Properties panel.

SELECT(values1, OR(($variable=values2, ("_all_"=$variable)))

  • values1: set to Units in column B (B:B).
  • $variable: set to your unique variable.
  • values2: set to Fruit in column A (A:A).
  • "_all_": string set to "_all_".

SELECT(B:B, OR(($fruit = A:A, ("_all_"=$fruit)))

If All is selected from a drop-down list, the values for all fruit are returned:
10, 15, 20.

SELECT and AND 

In this example, SELECT includes the AND function to restrict the data to match a date that falls between the last three months starting from today and today's date. In this example the current month is July, so the values for September and November are not returned because they do not match the criteria of the formula.

Note: Use the date format in your own data source.

SELECT(SLICE(values1), AND(OR($variable=SLICE(values2), "_all_"=$variable), BETWEEN(DATE(SLICE(values3),"d-MMM-yy"), DATE_ADD( TODAY (), unit, -3), TODAY()))))

  • values1: set to Units in column B (B:B).
  • $variable: set to your unique variable.
  • values2: set to Fruit in column A (A:A).
  • values3: set to Dates in column C (C:C).
  • unit: set to month.

SELECT(SLICE(B:B), AND(OR($fruit=SLICE(A:A), "_all_"=$fruit), BETWEEN(DATE(SLICE(C:C),"d-MMM-yy"), DATE_ADD( TODAY (), month, -3), TODAY())))

This formula returns 15 because it is the only value associated with a date that matches the criteria of this formula.

Related articles