Differences

This shows you the differences between two versions of the page.

--- transformations:rule [2015/01/29 23:16] – created dmitry
+++ transformations:rule [2021/04/01 18:30] – craigt
@@ Line 1: / Line 1: @@
-===== Rule =====
+{{ transformations:RuleAction.png}}
+====== RULE ======
+Category: Transform / Basic\\
-This transformation calculates new or replaces column by using nested IF...THEN...ELSE conditions. While the same result can be achieved using an expression with nested [[syntax:functions:if]] functions the use of Rule transformation is recommended for clarity and readability.
+\\
+=====Description=====
+This action calculates a new, 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.
-Rule transformation is somewhat similar to //switch// or //case// statements in popular programming languages.
+The order of conditions is significant as conditions are checked starting from 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 [[syntax:functions:if]] functions, the use of Rule action is recommended for clarity, readability, and the ease of rearranging the order.
+\\
+\\
+=====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.|
+\\
+=====Examples=====
+**Objective:** 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 condition above is met: Default to LOW
+**Output 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 |
+\\
+=====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.
+\\
+=====See also=====
+  * [[transformations:addcolumn|Calculate new column(s)]]
+  * [[transformations:replacecolumn|Modify columns(s)]]
+  * [[https://community.easymorph.com/t/place-stronger-conditions-first-in-the-rule-action/1113|Community: Place stronger conditions first in the Rule action]]