erwin Data Transformation 9.2.5 - User Guide

Data Formatting

Data Formatting

Format Date

The “Format Date” Transformation applies if it is required to transform a column with internal data type DATE from a specific source format (for example EUR date format) into a different target format (for example USA date format). If the column value can’t be transformed into the specific format, the user can choose the action to perform. The possible actions are; drop text (exclude from the following workflow steps only the values in the specified column where transformation can’t be performed), drop row (exclude from the following workflow steps the whole record for a specified column where the transformation can’t be performed in any specified column) or drop file (discard the entire file – no following operations will be performed). All these configurations may be done separately for each source field.         

Format Number

The “Format Number” Transformation applies if it is required to transform a column with internal data type NUMBER (integer or decimal) from a specific source format (for example EUR number format) into a different target format (for example USA number format). If the column value can’t be transformed into the specified format, the user can choose the action to perform. The possible actions are drop text (exclude only the values in the specified column where the transformation can’t be performed), drop row (exclude the whole record for a specified column where the transformation can’t be performed in any specified column) or drop file (discard the entire file and stop). All these configurations may be done separately for each source field.

Capital/Small letter

The “Capital/Small Letter” Transformation applies if it is required to change the string format of a specific column; the user can choose from Capitalized, To Upper and To Lower. The transformation will be applied to the specified column value. All these configurations may be done separately for each source field.

Suffix/Prefix

The “Suffix/Prefix” Transformation applies if it is required to add a specific string to a specific column value; the user can choose between Suffix and Prefix and insert the string to add as prefix/suffix to the column value. All these configurations may be done separately for each source field.

Replace Text

The “Replace Text” Transformation applies if it is required to replace a specific string value with another string value into a specific column values; the user can edit the text to be replaced and the text to replace it with. Some special replace can be obtained: in a cell containing multi-values separated by commas, for instance, using “\n” to replace “,” will result in a cell containing a list of those values separated by a newline. Also, wildcards can be used: for instance, when asked to replace “PROC*_” with “”, DT will delete this sub word from the cell value regardless of the dynamic part of it. All these configurations may be done separately for each source field.

Special parameter can be used for particular replace requirements:

-<%BLANK%> as text to be replaced: to replace empty text

-<%SYSDATE%> as text to replace with: to obtain datetime (at execution time) in UTC format or <%SYSDATE_EUR%> to obtain datetime in European format or <%SYSDATE_USA%> to obtain datetime in United States format (see here for date format descriptions).

Replace Value on Condition

The “Replace Value on Condition” Transformation applies if it is required to set a specific value if a condition is matched and another if it’s not. The user can edit the condition on the source column, choosing between “Equal”, “Not Equal”, “Less than (numeric value)”, “Greater than (numeric value)”, “Like” or “Not Like” options, and then editing the value to be compared with the source column value. Then the user may pick between the available columns to set the column target to be used in case the condition is matched or not. Parameters can be applied as previous transformation.

Rename and Order Columns

The “Rename and Order Columns” Transformation applies if it is required to change the name of a specific column, and or its order, in the output; the user can choose to rename a column, to give it a different order from the natural one (as coming from the source), or both. The order is not mandatory, while the given orders must not be conflicting. The transformation will be applied to the specified column value. All these configurations may be done separately for each source field.

Selected Value in List

The “Selected Value in List” Transformation applies if it there is a column containing a multiline text, as when exporting from a CM model an object and a list of associated objects, and it’s needed to have a single item of that list, either the first or the last. The user can set the option on the source column, choosing between “First value in List”, “Last value in List”. All these configurations may be done separately for each source field.

Multiple Rows Values to Multiline Cell

The “Multiple Rows Values to Multiline Cell” Transformation applies to get a list of items from a column containing different values, fixed other columns, grouping them in a multiline cell.

Typical use case: when exporting an intersection object type, with couples of associated objects, from a model, for a given couple obj1-obj2, obtain one single row with obj1 and a list of all the obj2 for that obj1 value.

Multiline Cell to Multiple Rows

The “Multiline Cell to Multiple Rows” Transformation applies to get, from a single multi-value (multiline) cell, one different row for one single different value in multiline value

          Typical use case: getting an object from CM, with properties and associations and obtain one row per associated object, for selected association type

Group data from datasets

The “Group data from datasets” Transformation applies when data belonging to different dataset must be grouped into one.

Typical use case: different lists of the same object type are provided by different data sources, and must be loaded into model, in a single workflow operation. This can be obtained defining:

•one dataset to be the master (this will contain all merged data): set this option on whatever column in the dataset

•one or more dataset providing data to append by column name (data will be appended to the master one by its columns name): set this option on whatever column in the dataset

•one or more dataset providing data to append by column position (data will be appended to the master one by its columns position): set this option on whatever column in the dataset

After the transformation, the master dataset will contain all data as per configured merging options, and can be used to send data to model:

Sort Data by Columns

The “Sort Data by Columns” Transformation applies if it is required to sort data by column contents. Use columns “Order Index” (1..4) and “Order Type” (“A to Z”, “Z to A”, “Numerical Ascending”, “Numerical Descending”) to have data sorted by the corresponding content and settings.

Compare and Replace

The “Compare and Replace” Transformation applies if it is required to compare two column values, and replace a third or fourth column value, if the condition is matched or not. Check the source column to be compared, set the condition, and the value for the comparison; select the true value (column to be used to replace value of the source, when condition is matched) and false value (column to be used to replace value of the source, when condition is not matched). After the transformation, the source column value will be replaced with the “true” value or “false” value, depending form the result of the condition evaluation.

Value Substring

The “Value Substring” Transformation applies if it is required extract part of a text from the value of a given field. Check the source column, the start from index, and the length of the text to extract. After the transformation, the source column value will be replaced with the extracted text. For instance, applying this transformation to “Application” with a start from equal to 1 and a length of 3, the output will be “App”. Providing a negative number for the start from parameter, the start will be considered from the end of the original string. For instance, applying the transformation to “New York” with a start from equal to -4 and a length of 4, the output will be “York”.

Data Structure Changing

Data Structure Changing

Combine Field

The “Combine Field” Transformation applies if it is required to create a new column as result of the combination of two other column values. The user can insert a name of the new column, the first field of the combine operation, a separator and the second field.

The user must indicate which is the data source to assign to the new column, using the “Adapter to assign column” drop-down list. This new column can be used by another transformation or by the mapping operation. Special chars like “\n” (combine with newline) can be used for specific requirements.

Split Field

The “Split Field” Transformation applies if it is required to create two new columns as result of the splitting an existing column value.
The user can insert the name of the two new columns and the separator that the system must use to perform the split operation. If you have more than one data source, you must indicate which is the data source to assign the new column to, using the “Adapter to assign column” drop-down list. This new column can be used by another transformation or by the mapping operation. Special chars like “\n” (split by newline) can be used for specific requirements.

Add Fixed value field

The “Add Fixed Value Field” Transformation applies if it is required to create a new column with the same value for all the entries. The user can insert a name for the new column and the value to assign to it. When you have more than one data source you must indicate which is the data source to assign the new column to, using the “Adapter to assign column” drop-down list. This new column can be used by another transformation or by the mapping operation. A parameter can be used to populate the new column with the system date and time: <%SYSDATE%> for UTC format, <%SYSDATE_EUR%> for EUR format, <%SYSDATE_USA%> for USA format (see paragraph 1.3.1 for date format descriptions).

Copy Column

The “Copy Column” Transformation applies if it is required to create a new column with the same values of another. The user can insert the name of the new column and the column value to assign to it, picking it from a list of available columns. The user must indicate which is the data source to assign the new column to, using the “Adapter to assign column” drop-down list.

This new column can be used by another transformation or by the mapping operation.

Drop Column

The “Drop Column” Transformation applies if it is required to delete one or more columns from the output. The user must check the column to be deleted and set the action to “DROP COLUMN”. The selected columns will not be included in the resulting output. A second option, “DROP DATASET”, will remove from the output the entire dataset that the column is in.

Data Filtering

Data Filtering

Fixed value manager

The “Fixed Value Manager” Transformation applies if it is required to filter the workflow data by some fixed value specified for a given column. The user can insert the format of the column, the filter operator between “Equal”, “Not Equal”, “Greater than”, “Less than”, “Like” or “Not Like”, “Equal sysdate”, “Later than (days ago)”, “Later than (hours ago)”, “Later than (minutes ago)”, “Earlier than (days ago)”, “Earlier than (hours ago)” , “Earlier than (minutes ago)”, “In (semicolon separated)”, “Not in (semicolon separated)” and the value to use as filter. For example, the condition “Not Like” and the value “a” will remove from the resulting dataset all the rows in which that column value does not contain “a” (case sensitive). All the configurations may be done separately for each source field.

When using “Like” and “Not Like”, wildcard “%” can be used to filter content containing text (“%CRM%”: only cells containing “CRM” match filter), starting with text (“CRM%”: only cells starting with “CRM” match filter), ending with text (“%CRM”: only cells ending with “CRM” match filter).

When possible, filters defined as first transformation in the list, for columns coming from a CM Object Type export, are executed at export time, allowing for more compact and less consuming workflow execution.

Interval value manager

The “Interval Value Manager” Transformation applies if it is required to filter the workflow data by a list of possible values specified for a given column. Users have to define the value list, and the separator between the list items. All these configurations may be done separately for each source field.

Distinct values

The “Distinct Value” Transformation applies if it is required to obtain distinct occurrences of data by a list of columns specified as the keyset. Users have to define the columns to be the keyset setting the action “DISTINCT”. The result will contain only one occurrence for any combination of values of the selected “DISTINCT” columns list.

Not All Null values

The “Not All Null values” Transformation applies if it is required to check that at least one value is not null in a list of columns specified by user. Users have to define the columns to be verified setting the action “Check value in Column”. The result will contain only rows containing at least one non null value in the selected columns.

In/Not In Dataset

The “In/Not In Dataset” Transformation applies if it is required to filter one dataset by the condition that a key column value is (not) in the allowed values list, provided by a slave key column in a second dataset. Users have to define for the master dataset a column as the “Key (master)” and a column as the “Key (Slave) – IN” or “Key (Slave) – NOT IN”. The result will contain rows in the master dataset, only if its master key column value (does not) exist in the slave dataset, in the slave key column.

Data Derivation

Data Derivation

Match Keys and Pick Values

The “Match Keys and Pick Values” Transformation (former “Format Values On Conditions”) applies if it is required to join values from different data sets, apply conditions to different column values, and then specify a result column value if the conditions (evaluated all together) are matched or not. For first, given two datasets, the user must choose a column for the first and a column for the second, setting one as “Key (master)” and the other as “Key (slave)” for the join operation. Then, for the remaining columns, the user may optionally define a “condition” as described for the “Replace value on condition” transformation: they have to all be matched to make the transformation condition be true. Lastly, the user can choose one or more columns as “result” column, thus choosing which column value to set for it if the condition is true and which if it’s false, picking them from the joined dataset columns. User may also define a single column as “Key (master)/Result”, to be used both as a key for the join, and to host the result when matching occurs.

Count Items In Column

The “Count Items In Column” Transformation applies if it is required to evaluate the number of item in a list, with an item per line in a multiline column. The user must give a name for the new column that will contain the number of items (one per line of the multiline cell) and select from a dropdown list the column containing the multiline text. The output column will contain the number of items (lines) in the selected multiline column.

Count Items In Rows

The “Count Items In Rows” Transformation applies if it is required to count the occurrence of a given keyset for several rows. The user must choose one or more columns to be the “Key (for data aggregation)”, a column to be the “Source column (to count items for keyset)” and a third column to be the “Result”. The output will contain a single row for any combination of keys; the “Result” column will contain the count of selected “Source” column in different rows for the same keyset. It can be used for instance to count associated objects for a master object.

Sum Values In Rows

The “Sum Values In Rows” Transformation applies if it is required to sum the numerical values in a selected column for several rows. The user must choose one or more columns to be the “Key (for data aggregation)”, a column to be the “Source column (to sum values from)” and a third column to be the “Result”. The output will contain a single row for any combination of keys; the “Result” column will contain the sum of values in the selected “Source” column in different rows (non numerical values will be skipped).

Sum Values In Columns

The “Sum Values In Column” Transformation applies if it is required to sum the numerical values in selected columns for one single rows. The user must choose one or more columns to be the “Source column (to sum values from)” and another column to be the “Result”. The “Result” column will contain the sum of values in the selected “Source” columns in same row (non numerical values will be skipped).

Divide Values In Two Columns

The “Divide Values In Columns” Transformation applies if it is required to get the division between values in two columns of a dataset . The user must choose one column to be the “Numerator“, a column to be the “Denominator” and a third column to be the “Result (number)” or “Result (percent)”. The output will contain in the “Result” column the division between values in “Numerator” and “Denominator” columns in the selected format (non numerical values will produced a blank result).

Two Levels Aggregation

The “Two Levels Aggregation” Transformation applies if it is required to sum the numerical values in a selected column for several rows, with two different levels of aggregation.

The user must choose one column to be the “Key (for first level aggregation)“,

•a column to be the “Result (for first level aggregation)”,

•a column to be the “Key (for second level aggregation)”,

•a column to be the “Result (for second level aggregation)”, and

•a column to be the “Source column (to sum values from)”.

The output will contain a single row for any combination of keys; the “Result (for first level aggregation)” column will contain the sum of values in the selected “Source column (to sum values from)” column in different rows, given a single “Key (for first level aggregation)” value, and  the “Result (for second level aggregation)” column will contain the sum of values in the selected “Source column (to sum values from)” column in different rows, given a single “Key (for second level aggregation)” value (non numerical values will be skipped).

Typical use case: A model contains a technical chain from server with  number of CPU, associated to IT services, associated with application, aggregated in platforms: exporting the two intersection object types, and using the “Sum Values in Rows” for association between servers (with CPU) and IT Services, collecting the CPU for IT Service in the first dataset (association between platform and application), using the IT service as key in “Match keys and pick values” transformation, this output can be obtained: