RULE

Category: Transform / Basic

Description

This action calculates a new column or replaces an existing column, by using one or several ordered IF…THEN…ELSE conditions. The conditions are checked one by one until a satisfying condition is found. When a satisfying condition is found, the result is calculated and all other conditions are ignored. When no satisfying condition is found, the default calculation is applied.

Action settings

Column options

Setting	Description
Create new column	This option creates a new column with the results of the rule. Enter a name for the new column in the text box at the right.
Replace existing	With this option, select the column that will contain the results of the rule, overwriting the existing values.

Calculated as settings

Setting	Description
if	An expression that evaluates to either TRUE or FALSE which determines whether the then statement will be processed (i.e., the then will run when the if expression is TRUE.
then	The value inserted into the column when the if condition is TRUE. This can be entered as an expression or static value. Options: this is an expression or this is a text or number.

You can add more if/then conditions using the Add more rules option. An else will be inserted between if/then pairs, indicating that later conditions are only run if previous conditions evaluate to FALSE. The if/then rules are evaluated in the order entered until one evaluates to TRUE, and the rest are ignored.

No conditions met options

Setting	Description
Make empty	If none of the if statements evaluate to TRUE, enter an "empty" value.
Default to	Options: it's an expression or it's a text or number. Enter either an expression to be evaluated, or a static value, in cases where none of the if statements evaluate to TRUE.
Keep original value	If none of the if statements evaluate to TRUE, retain the original value.

Remarks

The order of conditions is significant as conditions are checked starting with the first (top). Therefore, conditions that come first have priority over conditions that come later.

While the same result can be achieved using an expression with nested if functions, the use of Rule action is recommended for clarity, readability, and the ease of rearranging the order.

Examples

Example: Add a column describing the average spending potential in each country as low or high, based on the disposable income. The spending potential is high if the disposable income is $40,000 or more.

Source table: Average income and deductions

Country	Gross income	Disposable income	Compulsory deductions
United States	56067	45582	18.7%
Australia	51050	42617	16.52%
Switzerland	53716	48414	9.87%
Canada	45896	37469	18.36%

Action parameters:

Create new column: Spending
Calculated as: if ([Disposable income] > 40000), then HIGH.
If none of the conditions above is met: Default to LOW

Result table:

Country	Gross income	Disposable income	Compulsory deductions	Spending
United States	56067	45582	18.7%	HIGH
Australia	51050	42617	16.52%	HIGH
Switzerland	53716	48414	9.87%	HIGH
Canada	45896	37469	18.36%	LOW

Community examples

Import figures in “scientific format” (Project; Module: Main; Group: Tab 1; Table: Table 1; Action position: 2)
How to look up and replace a string of text (Project; Module: Main; Group: Tab 1; Table: Table 3; Action position: 5)
Aggregation of consecutive date ranges (Project; Module: Main; Group: Tab 1; Table: Easymorph Patient Example Data.xlsx;
Action position: 6)
Partial or fuzzy lookup (Project; Module: Main; Group: Tab 1; Table: Lookup; Action position: 4)
How to report validation failures? (Project; Module: Main; Group: Tab 1; Table: Validate data; Action position: 2)

Shortcuts

Fast ways to create the action:

Right-click on a column heading, and select "Modify", then "With rule…" from the submenu.
Click the drop-down arrow in a column heading and select "Modify", then "With rule…" from the submenu.

EasyMorph Help

Table of Contents