`if(C1;R1;C2;R2;...;D)`

Returns the value corresponding to the first condition that evaluates to true; if no conditions are true, it returns the specified default value.

Note: The 1010data function if(C1;R1;C2;R2;...;D) that is part of the expression language is different from the <if> block code construct. For more information, see the Additional information section below.

If you have a large number of arguments in if(), you may want to consider using iff(). iff() is potentially faster and more memory efficient.

Syntax

if(C1;R1;C2;R2;...;D)

Input

Argument	Type	Description
`C[N]`	integer	A scalar value or the name of a column The value of `C[N]` can only be `0` or `1` or an expression that evaluates to one of those values.
`R[N]`	any	The value to return if `C[N]` evaluates to `1` The values specified for all instances of `R` must be the same type (integer, decimal, or text).
`D`	any	The default return value (if none of the instances of `C` evaluate to `1`) The value specified for `D` must be the same type as the values specified for all instances of `R`.

Note: There must be an odd number of arguments (i.e., a series of C and R pairs and one D argument).

Return Value

Returns a numeric or text value (depending on the type of the elements specified for R and D) corresponding to the following:

If C1 is 1, return R1; else if C2 is 1, return R2; and so on.
If none of the conditions specified by C[N] evaluate to 1, return D.

If any of the values corresponding to C[N] are N/A, an error is returned.

Sample Usage


`C1`	`R1`	`D`	`if(C1;R1;D)`
0	5	10	10
1	'true'	'false'	'true'
0	100	NA	NA


`C1`	`R1`	`C2`	`R2`	`D`	`if(C1;R1;C2;R2;D)`
0	'one'	1	'two'	'three'	'two'

Example

Let's use the table pub.demo.retail.item to illustrate the use of if(C1;R1;C2;R2;...;D).

Let's say we have three stores and that store 1 is located in New York, store 2 is located in Miami, and store 3 is in London. To add a column to our table that corresponds to the location for each store, we can use the following Macro Language code:

<willbe name="location" value="if(store=1;'New York';store=2;'Miami';store=3;'London';'Not a valid location')"/>

This will give us the following new column:

Since, in our example, there are no values in the store column that are not 1, 2, or 3, the default value ('Not a valid location') is never used.

Let's see an example where we use the default value.

Let's say we want to change all the negative values in the sales column (which correspond to returns) to N/A; otherwise we want to keep all the other values the same. We can do this with the following code:

<willbe name="xsales" value="if(sales<0;NA;sales)"/>

This will result in the following:

You can see that the first row, whose sales column had a value of -5, now contains an N/A value in the newly created xsales column. All the other values in that column have the same value as the sales column.

Additional information

The 1010data function if(C1;R1;C2;R2;...;D) that is part of the expression language is different from the <if> block code construct.

The if(C1;R1;C2;R2;...;D) function in the expression language takes a series of conditions and returns the value corresponding to the first condition that evaluates to true (or the final value if none of the conditions are true).

The <if> construct in block code, on the other hand, takes a single condition and expands its contents only if the condition is true. If it contains <then> and <else> clauses, it expands the contents of the <then> clause if the test condition is true, otherwise it expands the contents of the <else> clause. For more information, see <if>.

Note that the block code <switch> statement implements behavior analogous to that of the if(C1;R1;C2;R2;...;D) function. For more information, see <switch>.