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]] |