| Both sides previous revisionPrevious revisionNext revision | Previous revision |
| transformations:rule [2019/05/30 06:58] – dmitry | transformations:rule [2021/07/19 02:30] (current) – [Examples] craigt |
|---|
| ===== Rule ===== | {{ transformations:RuleAction.png}} |
| | ====== RULE ====== |
| | Category: Transform / Basic\\ |
| |
| This action calculates new or replaces 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. | \\ |
| | =====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.\\ |
| |
| 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 after them. | \\ |
| | =====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 [[syntax:functions:if]] functions, the use of Rule action is recommended for clarity, readability, and the ease of rearranging the order. | 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.\\ |
| |
| EXAMPLE | \\ |
| | =====Examples===== |
| |
| **Source table:** Average income and deductions | **Example:** Add a column describing the average spending potential in each country as low or high, based on the disposable income. |
| | |
| ^ 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% | | |
| | |
| **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. | The spending potential is high if the disposable income is $40,000 or more. |
| |
| **Transformation parameters:** | **Source table:** Average income and deductions |
| * 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 ^ | ^ Country ^ Gross income ^ Disposable income ^ Compulsory deductions ^ |
| | United States | 56067 | 45582 | 18.7% | HIGH | | | United States | 56067| 45582| 18.7%| |
| | Australia | 51050 | 42617 | 16.52% | HIGH | | | Australia | 51050| 42617| 16.52%| |
| | Switzerland | 53716 | 48414 | 9.87% | HIGH | | | Switzerland | 53716| 48414| 9.87%| |
| | Canada | 45896 | 37469 | 18.36% | LOW | | | 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==== |
| | * [[https://community.easymorph.com/t//1530/2|Import figures in “scientific format”]] ([[https://community.easymorph.com/uploads/short-url/poEu3Fy9uLb9DdlWKAPN4VoS8XU.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //2//) |
| | * [[https://community.easymorph.com/t//1551/2|How to look up and replace a string of text]] ([[https://community.easymorph.com/uploads/short-url/dvCtD7gg0AHUtXZ7tmQmuHflSYU.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 3//; Action position: //5//) |
| | * [[https://community.easymorph.com/t//1686/2|Aggregation of consecutive date ranges]] ([[https://community.easymorph.com/uploads/short-url/xyC5nPd52hGrMiRXjp5w1RVUTYL.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Easymorph Patient Example Data.xlsx//;\\ Action position: //6//) |
| | * [[https://community.easymorph.com/t//1697/2|Partial or fuzzy lookup]] ([[https://community.easymorph.com/uploads/short-url/nfnr4o7wX53X2SEaJU5NJ5AzAGt.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Lookup//; Action position: //4//) |
| | * [[https://community.easymorph.com/t//1876/2|How to report validation failures?]] ([[https://community.easymorph.com/uploads/short-url/x8xqV8QqQbnnI467dxWdhLbXnFr.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Validate data//; Action position: //2//) |
| |
| ** See also** | \\ |
| | =====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. |
| |
| * [[https://community.easymorph.com/t/place-stronger-conditions-first-in-the-rule-action/1113|Community: Place stronger conditions first in the Rule action]] | \\ |
| | =====See also===== |
| | * [[transformations:addcolumn|Calculate new column(s)]] |
| | * [[transformations:replacecolumns|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]] |
