The SELECT function allows you to select a subset of data based on a specified criteria.
The syntax for the SELECT function 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, and is typically a comparison between values. Note: it is unnecessary to use the IF function in the condition parameter.|
The data and condition parameters must be the same size because each item in the condition array is applied to each item at the same position in the data array.
To learn more about the SELECT function, you can watch these videos!
Using the following data source, SELECT( A:A, (B:B = 15) ), selects Oranges from column A, because the corresponding value in column B equals 15.
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
SELECT( A:A, (B:B = 15 ) ) performs the following comparisons:
Select Fruit if (Units = 15): since (Units = 15) returns false, Fruits is not selected.
Select Apples if (10 = 15): since (10 = 15) returns false, Apples is not selected.
Select Oranges if (15 = 15): since (15 = 15) returns true, Oranges is selected.
Select Pears if (20= 15): since (20 = 15) returns false, Pears is not selected.
The following examples show you how to use the SELECT function, with a variable created in a User Input Control component, when selecting data in your Klips.
In this example, the SELECT function returns the value in column B that matches the fruit-type selected from a drop-down list.
For example, if you select Apples, the value returned is 10.
SELECT( B:B , ( variable = A:A ))
In this example, the SELECT function includes the OR function to give the user the option to return all fruit-types. If "All" is selected from a drop-down list, the values for all fruit-types are returned.
For example, Apples = 10, Oranges = 15, and Pears =20.
Note: The SLICE function removes the column header.
SELECT( SLICE( B:B ) , OR( ( variable = SLICE( A:A ) , ( "All" = variable ) ) )
In this example, the SELECT function includes the AND function to restrict the data to the last three months starting from today's date.
For example, as the current month is November, the value for July is not returned.
Note: Use the date format in your own data source.
SELECT( B:B , AND( OR( ( variable = A:A ) , ( "All" = variable ) ), BETWEEN( DATE( C:C , "d-MMM-yy" ) , DATE_ADD( TODAY( ) , month , -3 ) , TODAY( ) ) ) )
To see an example of a Klip that uses the SELECT function with a variable, and the AND and OR functions, refer to the following article: Common User Input Control scenarios: Displaying total boxes fruit sold in the last three months