The If Then transformation allows for you to make changes to your table using standard If Then statements. Like all other Transformations, the If Then Transformation can be applied to either a Table or a Dataset.
Follow the steps outlined below to create an If Then Transformation:
1. Open the Table or Dataset you would like to transform by clicking on its title, under the Tables or Datasets heading.
2. When the Table or Dataset opens, a sidebar titled Transformations will appear on the left side of the screen. Any existing transformations will be displayed under that heading.
To create a new transformation, click the + in the sidebar (note: if there are several existing transformations currently applied, you will need to scroll down to view the +).
Select If Then Statement from the list that appears. This will open the statement editor.
3. Name the statement you would like to create (this helps keep your transformations organized).
4. If Then Statements consist of two elements: a trigger, and an action. A trigger examines a column and determines if it meets the condition outlined in the statement.
If the trigger is met, then the actions are carried out. If the trigger is not met, then no action is carried out.
Begin by outlining the rules for the trigger. Select the column you would like the trigger to evaluate from the drop-down under the When heading and select the condition to apply to that column.
5. If you would like to add multiple triggers, you can click the + Add Trigger button. If more than one trigger is added, you will need to determine if either one or both of the triggers need to be fulfilled in order to execute the action. This can be done by selecting And/Or from the drop-down list between the trigger statements. Selecting And states that both trigger statements need to be true before the actions will be carried out. Selecting Or states that any one of the trigger statements need to be true for the action to be carried out.
You can delete any unwanted triggers by clicking on the x located to the right of the statement.
6. Once your triggers are established, you will need to specify the actions to be carried out if the triggers are met.
Under the Then heading, select either Set or Run Formula from the drop-down list. By selecting Set you can change the value in a specified column. By selecting Run you can execute a custom formula and apply the output to a specified column.
The If Then transformation does not support ‘else’ statements (an action to be carried out when an If Then statement is false). If you would like to use an ‘else’ statement, you can either create a new If Then statement, or can use the ‘ifelse’ formula to carry out the desired action.
Additional actions associated with that trigger can be set by clicking the + Add Action button.
7. Once your If Then Statement is complete, click the Publish button in the top right corner to save the statement. If you wish to cancel this statement, click the Revert button in the top right corner.
Once your Transformation has been published, it will be applied to your Table or Dataset. This process can take several minutes, but once it is complete, the transformed data should show up in your Table or Dataset.
If you create a new column in an existing Dataset, specifically one that is referenced in an active Workbook, you may need to open the Column Properties tab and make sure the newly created column is searchable. This will make the column available for use in Workbooks.
Note - all of the Transformations in Numetric are non-destructive, meaning they do not permanently change the data. Any transformation can be easily undone by deleting it from the list of Transformations, reverting your data to its previously un-transformed state.