Calculated Fields

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

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:

The layout of the dialog when no calculated fields have been defined.

If you have already created calculated fields, the dialog will look like this:

Element

Use

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

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

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

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

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.