IF macro

The IF macro is available in HCL® Campaign and HCL Interact.

Syntax

IF(predicate_col, then_value)
IF(predicate_col, then_value, else_value)

Parameters

predicate_col

A column of boolean values or an expression evaluating to a single column of boolean values. Boolean values are interpreted as zero or non-zero. This column should contain at least as many rows as the data range from which data is being extracted.

then_value

The value(s) to return if the corresponding row of predicate_col contains a non-zero value. This can be a constant value, a column, or an expression evaluating to any of the above. See Macro function parameters for HCL Campaign for the format definition of then_value (same as data).

else_value

If this optional parameter is provided, it is returned if the corresponding row of predicate_col contains a zero. This can be a constant value, a column, or an expression evaluating to any of the above. If else_value is not provided, a zero is returned whenever predicate_col evaluates to false. See Macro function parameters for HCL Campaign for the format definition of else_value (same as data).

Description

IF evaluates the expression in predicate_col and returns then_value if the expression is true, or else_value if the expression is false. It returns the same number of columns in then_value and else_value. The new column(s) will contain the corresponding row value(s) from then_value if the value of predicate_col is non-zero. If else_value is provided, it is returned when the value of predicate_col is zero. If else_value is not provided, zero is returned.

Since IF operates on a row-by-row basis, it produces a result for each row up to the last value of the shortest column (that is, the shortest column out of predicate_col, then_value, and else_value).

Note: Generally, you will want to create a predicate column using one of the comparison macro functions (for example, ==, >, <, ISEVEN, ISODD, and so on).

Examples


`TEMP = IF(1, V1)` Creates a new column named `TEMP` containing a copy of column `V1`.
`TEMP = IF(V1, 1, 0)` Creates a new column named `TEMP`, where each value is one if the corresponding value of column `V1` is non-zero, otherwise zero.
`TEMP = IF(V3, V1, V2)` Creates a new column named `TEMP`, where each value is copied from column `V1` if the corresponding value of column `V3` is non-zero; otherwise the value is copied from column `V2`.
`TEMP = IF(ABS(V1-AVG(V1)) < STDV(V1), V1)` Creates a new column named `TEMP` containing each value in column `V1` that is less than one standard deviation away from the mean.
`TEMP = IF(V3[20:30], V1[30:40], V2)` Creates a new column named `TEMP` containing values for rows 10-20. Each value is copied from column `V1` (cells 10-20) if the corresponding value of column `V3` (cells 30-40) is non-zero; otherwise the value is copied from column `V2` (cells 1-11).