Data Operators

A data operator takes data from any other dataset or data operator as its input. Dependent on the selected data operator you can combine, transform oder change the input data.

Aggregation

The aggregation operator allows you to aggregate keyfigure values, without any or along one or more predefined characteristics. Following aggregation operations are possible:

• AVG
• COUNT
• MAX
• MIN
• SUM

Compare

The compare operator allows you to compare two different lists of values and/or attributes. First, you connect the two sources you want to compare and then you select the columns to compare.

Columns can be 1) non-value and 2) value columns. The comparison of non-value columns will result in a list where all selected columns are equal. The comparison of value columns will calculate the difference between the values and results in a list where the difference is not zero.

Computation

With the computation operator you can do more complex calculations using R.

Filter

The filter operator allows you to filter the result set of a connected source. It is possible to select one ore more columns with following comparison operators:

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal
[]	Between
.*.	Contains
*.	Starts with
.*	Ends with
is null	Is null

You can also toggle the sign of a filter value to switch between include and exclude.

Further, Model parameters can be referenced:

1. Connect the desired Model parameter to your filter node
2. Select the parameter using the Source dropdown

warning

The Model parameter data type and the data type of the column to be filtered must match. Operators and signs are set via the Model parameter value input.

Formula

You can create either new columns or replace existing ones using formulas. These formulas can also be nested. The following formulas are available:

Formula	Description
ADD_DATE(<date> as date,<number> as number)	Add days to a given date or substract in case of a negative number
CONCAT(<text> as string,<text> as string,...)	Concatenate two or more strings
GET_DATE(<year> as number,<month> as number,<day> as number)	Create a date from a given input
GET_DATETIME(<year> as number,<month> as number,<day> as number),<hour> as number,<minute> as number,<second> as number, <optional millisecond> as number)	Create a date-time from a given input
GET_MONTH(<date> as date)	Get the month from a given date
GET_QUARTER(<date> as date)	Get the quarter from a given date
GET_TIME(<hour> as number,<minute> as number,<second> as number, <optional millisecond> as number)	Create a time from a given input
GET_YEAR(<date> as date)	Get the year from a given date
IF <condition> THEN <result on true> ELSE <result on false>	Conditional column
LEFT(<text> as string,<length> as number)	Extract characters from a string starting from the left
LENGTH(<text> as string)	Returns the length of a string
LOWERCASE(<text> as string)	Transform provided string to upper case
NOW	Returns the current time
REPLACE(<text> as string,<old> as string,<new> as string)	Replace a substring within the provided string
RIGHT(<text> as string,<length> as number)	Extract characters from a string starting from the right
ROW_NUMBER(<COL1> as any,<COL2> as any,...)	Rows are numbered starting from 1 in the order defined by order criteria
SUB_DATE(<date> as date,<number> as number)	Subtract days from a given date
SUBSTRING(<text> as string,<start> as number,<length> as number)	Get the substring from a given string input
TODAY	Returns the current date
TODAY_NOW	Returns the current date and time in the date-time format
TO_DATE(<date> as datetime)	Extract the date value from a date-time value
TO_DATETIME(<date> as date, <time> as time)	Merge a date value and a time value into a date-time value
TRIM([LEFT or RIGHT or BOTH], <text> as string[, character(s)])	Remove leading or trailing white-spaces or defined characters from left, right or both sides
UPPERCASE(<text> as string)	Transform provided string to upper case

Further, you can use constants (e. g. DateConstant), logical operators (and, or, not) and relational operators (>, >=, <, <=, <>).
In addition, you can encapsulate a string using single quotes (e. g. 'text').

Join

The join operator allows you to join two differen datasets. The join criteria can consist of one or more column(s) from both sides. The join direction can be selected by the following buttons:

• Inner Join is a type of relational database operation that combines rows from two or more tables based on a related column between them. The result of an inner join includes only the rows that have matching values in the specified columns of both tables.

• Left Join returns all records from the left dataset, and the matching records from the right dataset. The result includes 0 records from the right side, if there is no match.

Further, you can either use the default prefix setting or define your desired prefix for both sides of the join.

Order By

The Order By operator allows you to specify order criteria and order direction of a connected dataset. You can decide whether the direction should be ascending or descending. The definition of the order can also be combined for several columns.

Transformation

A sequence of different transformation steps can be applied to the dataset of a connected source. The following transformation types are available:

Type conversion

Allows you to convert data types, e.g., from string to date or number or from date to string, etc.

Rename columns

Allows you to rename columns.

Reorder columns

Allows you to reorder columns.

Select columns

You can select specified columns from the connected source.
Hint: Use the button Select all Columns and then deselect the columns that should not be included.

Remove columns

You can remove specified columns from the connected source.
Hint: Use the button Select all Columns and then deselect the columns that should not be removed.

Pivot columns

Allows you to pivot the dataset of a selected source. You must define the identifier column with the values that you want to pivot and the value columns that are to be summarized based on the pivoted values of the identifier column. You must also select which values of the identifier column are to be pivoted. All unassigned values are summarized in an unassigned column, which is displayed when 'Show unassigned columns' is checked.

note

It is not recommended to use the Pivot columns Transformation, as users must manually specify which values of the identifier column should be pivoted. Therefore, each time new values are added to the identifier column, these values will be added to the unassigned column if the Transformation is not adjusted accordingly in the Model. Further, if users specify values to be pivoted that are not contained in the identifier column, these values are pivoted and totaled to 0.

Unpivot columns

Allows you to unpivot the dataset of a selected source. By specifying the columns to be unpivoted you can transform the result into a flat table. Values of the selected columns will be added as new records to the dataset, summarized in a single value column. To identify the value of an unpivoted column a new column is added containing the original column name as value.

warning

If you delete a transformation step in the middle of a transformation sequence, all subsequent steps will be deleted.

Union

With a union operator it is possible to concatenate two or more different lists.
You can change 1) the order in which the tables will be merged and 2) the order of the columns by drag and drop. The first Virtual Dataset which is connected to the Union Node defines the default result table specification (number of columns, order, names and types).

Special cases:

• If the upper table includes a column that is not present / cannot be matched in the lower table, then the lower table values are set with predefined (e. g. NULL, Empty String) or customized values.
• If the lower table includes a column that is not present / cannot be matched in the upper table, then this column is defined as to be ignored. Of course, you can add a column for this specific union criteria. Values can then be created in the upper table as described above.
• You can also create and customize new columns when there is no column present in either the upper or the lower table.