CHOOSE function

This topic describes the formula syntax and usage of the CHOOSE function in the Data Quality Execute Formula transform.

Description

Chooses a value from a list of values.

Syntax

CHOOSE(index_number, value1, value2, ...)

The CHOOSE function syntax has the following arguments:

  • index_number Required. Specifies which value argument is selected. Index_number must be a number between 1 and 254, or a formula or reference to a column containing a number between 1 and 254.
  • value1, value2, ... is required, subsequent values are optional. 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on index_number. The arguments can be values, functions, formulas, or column references.

Notes

  • If index_number is an array, every value is evaluated when CHOOSE is evaluated.
  • An argument to CHOOSE can be a value, or a column reference, formula, or function that returns a value.

Example 1

Table 1. Dataset record
Amphibian Reptile Bird Mammal
Salamander Tortoise Eagle Wolf
Formula Description Result
CHOOSE(2,[Amphibian],[Reptile],[Bird],[Mammal]) Value of the second field argument (value of field [Reptile]). Tortoise
CHOOSE(4,[Amphibian],[Reptile],[Bird],[Mammal]) Value of the fourth field argument (value of field [Mammal]). Wolf
CHOOSE(3,[Amphibian],[Tortoise],CONCAT([Amphibian]," ",[Reptile])) Value of the third list argument. Salamander Tortoise

Example 2

Table 2. Dataset record
Value1 Value2 Value3 Value4 Value5 Value6
44 21 2 11 18 4
Formula Description Result
SUM([Value2]:CHOOSE(2,[Value3],[Value4],[Value5],[Value6])) Sums the range [Value2]:[Value4]. The CHOOSE function returns [Value4] as the second part of the range for the SUM function. 32
CHOOSE(2,[Value1],SUM([Value2]:[Value4]),[Value5],[Value6]) Sums the range [Value2]:[Value4] (21+11) as the second argument. The CHOOSE function then returns sum. 32