Calculated Fields
- Sylvain Paillard
- Ivo Eid (Unlicensed)
1. General
This page only gives an explanation about the calculated fields and their editing. For general information regarding the Taxonomy, please visit the main Taxonomy page.
A created field can contain as many calculated fields as you want. The editing for this takes place in the calculated fields dialog.
1.1. Calculated field dialog
To create, modify and delete your calculated fields, use the calculated fields dialog.
1.1.1. Opening the dialog
The table on the Taxonomy view contains a column titled Calculation. There is a button with a calculator icon for each available field.
Element | Use |
|---|---|
Calculation | Open the dialog for editing the calculated fields.  |
1.1.2. Using the dialog
If you have not yet created any calculated fields, the dialog will look like this:
If you have already created calculated fields, the dialog will look like this:
Element | Use |
|---|---|
| Creates a new calculated field with default values. |
| Deletes a calculated field. |
Name | Allows you to set the name of the calculated field. |
Type | Allows you to set the type of the calculated field |
Status | Allows you to set whether the calculated field is active or not. |
1.1.3. Saving and closing the dialog
When the calculated fields are completely filled in, you can close the dialog by pressing save. Please make sure that all entered values are correct and correspond to the required formatting. Otherwise you will be alerted that something is wrong.
You can also simply close the dialog and get informede that your changes will not be saved.
Element | Use |
|---|---|
Save | Closes the dialog, checks the required formatting and, if necessary, informs what is not correct. If everything is correct, the dialog is closed and the calculated fields are saved to the corresponding field. |
Cancel | Closes the dialog, but warns that any changes will not be saved. |
1.2. Sources
Element | Use |
| Creates a new source with default values. |
| Deletes a source. |
Name | Allows you to set the name of the source. |
Project | Allows you to set the project of the source. |
Dataset | Allows you to set the accessed dataset of the source. |
Table | Allows you to set the accessed table of the source. |
Field | Allows you to set the accessed field of the source. |
Left Join | Allows you to set whether if it's a left join or not. |
1.3. Transformations
Element | Use |
| Creates a new transformation with default values. |
| Deletes a transformation. |
Type | Allows you to set the type of the transformation. This is either function, case when or fix. |
Depending on what type is set for the transformation, other values must be filled in.
1.3.1. Transformation type: function
This section describes the transformation when it has the type function.
Element | Use |
Parameters | Allows you to set the parameters of the function. By Default it has the value "$" to indicate the usage of the standard variable. |
Values | Allows you to set the name of the function calculation. You can choose between length, divide, add, multiply, substract and concat |
1.3.2. Transformation type: case-when
This section describes the transformation when it has the type case-when.
For more on the conditions, see chapter 1.4.
Element | Use |
Conditions | Allows you to define several conditions. |
Then | Allows you to define several transformations which will be executed if your defined conditions are true. |
Else | Allows you to define several transformations which will be executed if your defined conditions are not true. |
1.3.3. Transformation type: fix
This section describes the transformation when it has the type fix.
Element | Use |
Values | Allows you to define several values that must match the name. |
Name | Allows you to define a name to be searched for in the defined table. |
1.4. Conditions
The condition editor appears for each defined source and for each transformation with the type case-when.
Element | Use |
Type | Allows you to set the type of the condition. This is either simple, and or or. |
Depending on what type is set for the condition, other values must be filled in.
The conditions offer the choice to either define a simple condition with a field, an operator, a list of values and a check box for any values or to chose an and or or condition which will enable you to define a list of conditions.
1.4.1. Condition type: simple
Element | Use |
Field | Allows you to define the field where the defined condition is executed. |
Values | Allows you to define a list of values for the defined condition. |
Operator | Allows you to define the operator of the defined condition |
Any Values | Allows you to set whether any values can be used. |
Depending on the operator you selected, the condition is checked differently. The following table lists the operators and their meaning. An operator compares the value to its left with the one to its right.
Operator | Meaning |
|---|---|
> | Greater than |
< | Smaller than |
>= | Greater than or equal to |
<= | Smaller than or equal to |
= | Equal to |
1.4.2. Condition type: and
If the type is and, then you can define several conditions, which themselves can be simple, and or or.
Your defined and-condition is only true if ALL of its conditions are true.
Element | Use |
|---|---|
| Allows you to add a condition to your defined conditions. |
| Allows you to remove the condition above the button. |
1.4.3. Condition type: or
If the type is or, then you can define several conditions, which themselves can be simple, and or or.
Your defined or-condition is only true if at least one of its conditions are true.
Element | Use |
|---|---|
| Allows you to add a condition to your defined conditions. |
| Allows you to remove the condition above the button. |
2. Example
To simply explain how it works, let's look at a small example first.
Let's consider this case as an example: you want to create a new calculated field that sets all products with a string value 'cheap' if the product has a price below 20 CHF, and 'expensive' otherwise.
2.1. Defining one or more sources
First, you need to define one or more sources for your field calculation (in our example, the source would load the price of the product):
Let's assume that the product is on the database named "bx-bdp-53322", on the dataset "views" and in the table "product_property". The field you are looking for is then, for example, "property_value". Depending on that you can also choose if a left join has to be made. For the name you can take any value.
The dialog with the created source would look like this:
Then you need to set a condition that will execute the defined transformations if there is also a value for the price of the product. Let's assume that the field is called "property_name".
2.2. Defining one or more transformations
Then you must indicate what transformations should be applied for the calculation of the final value (in our example above, there must be a check if the price is < 20.- CHF in which case the value should be set to 'cheap' and otherwise to 'expensive'.
We assume that the value, whether it is cheap or expensive, is stored as "property_information".
For this we choose a transformation with the type case-when.
This checks if our selected field has a value of less than 20.
Then you define transformations that will be executed according to the condition.
In our case, a transformation comes in the then parameter. This is executed only if the condition of the transformation is true, so the price of the product is below 20 CHF.
Then you need to define a transformation that should be executed if the condition is not true, that is, if the price of the product is above 20 CHF.
In our case you need to define a transformation in the else parameter.
Using this calculated field, you should be able to change the value automatically based on the price of the product.