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).