Intro
There are six available tiles in the Utility tab of Magic ETL DataFlows. These include:
- Add Constants
- Add Formula
- Select Columns
- Set Column Type
- Set Column Value
- Value Mapper
For information about creating a Magic ETL DataFlow, see Creating a Magic ETL DataFlow.
For information about the Data Center, see Data Center Layout.

Add Constants Tile
The Add Constants tile lets you add a column with constant values.
To configure the Add Constants tile,
-
Click the Add Constants tile in the canvas.
-
(Optional) Rename the tile by clicking
, then entering the name you want.
-
Enter the name of the new column.
-
Select the column type.
Column Type
Description
Text
Contains text and numbers (which are treated as text).
Decimal
Contains numbers in decimal notation.
Decimal (Fixed)
Contains decimal numbers with a fixed number of digits after the decimal point.
Whole Number
Contains numbers without a decimal part.
Date
Contains date values.
Date and Time
Contains date and time values.
-
Enter the constant value, date, date and time, or leave it blank.
Add Formula Tile
The Add Formula tile lets you add a column with constant values.
To configure the Add Formula tile,
- Click the Add Formula tile in the canvas.
- (Optional) Rename the tile by clicking
, then entering the name you want.
- Enter the name of the new column.
- Enter the formula you would like to perform.
For more information on how to write functions for the Add Formula tile, see Writing Formulas in Magic ETL.
Alter Columns
The Alter Columns tile lets you change the data type of a column (for example, from number to text). For more information about data types, see Understanding Chart Data.
Example
For example, the Alter Columns tile transforms this data...
... to this...
... using this configuration:
To configure the Alter Columns tile,
-
Click the Alter Columns tile in the canvas.
-
(Optional) Rename the tile by clicking
, then entering the name you want.
-
For each column where you want to set the data type, do the following:
-
Select the column you want to set.
For example, "Order Date String". -
Select the data type you want for the column.
Data Type
Description
Text
Contains string values.
Decimal
Contains numbers in decimal notation.
Decimal (Fixed)
Contains decimal numbers with a fixed number of digits after the decimal point.
Whole Number
Contains numbers without a decimal part.
Date
Contains date values.
Date and Time
Contains date and time values.
- (Conditional) If you want to add another column, then click Add Column.
-
Select Columns Tile
The Select Columns tile lets you select columns to include, reorder columns, and rename column headings.
Example
For example, the Select Columns tile transforms these columns in this data...
... to this...
... using this configuration:
Video - Renaming Fields with the Select Columns Tile in the new Magic ETL
To configure the Select Columns tile,
-
Click the Select Columns tile in the canvas.
-
(Optional) Rename the tile by clicking
, then entering the name you want.
-
Select the columns you want to include (or exclude) by doing any of the following:
-
Click Add Column, select a column you want. Repeat to add other columns.
-
Click Add All Columns, then exclude columns by clicking the X associated with the column.
For example, remove "Order Quarter".
-
-
Reorder columns by clicking
and dragging a column to the order position you want.
For example, moving "Order Date" after "Product Container". -
For each column heading you want to rename, enter the new name of the column heading.
For example, renaming "Product Base Margin" to "Base Margin".
Set Column Value
The Set Column Value tile lets you replace the value of a column with the values in another column.
Notes:
- You can only replace values in one column with values from another column that have the same data type. For example, you can copy from a column with string values to another column with string values.
- Both columns must already exist in the DataSet.
For information about replacing text values using other tiles, see Replace Text and Value Mapper.
Example
For example, the Set Column Value tile transforms this data...
... to this...
... using this configuration:
To configure the Set Column Value tile,
-
Ensure that the column with values you want exists in the DataSet.
-
Click the Set Column Value tile in the canvas.
-
(Optional) Rename the tile by clicking
, then entering the name you want.
-
For each column with values you want to replace, do the following:
-
Select the column with values you want to replace.
For example, "Product Base Margin". -
Select the column with the values you want.
For example, "Net Margin". -
(Conditional) If you want to add a column, then click Add Column.
-
Value Mapper Tile
The Value Mapper tile lets you search and replace string values in a specific column, according to pairs of string values you enter. (You might use the Value Mapper action for replacing abbreviations or converting language codes.) You can either have replacement values overwrite an existing column or be in a new column.
For information about replacing text values using other actions, see Replace Text and Set Column Value.
Example
For example, the Value Mapper tile transforms this data...
... to this...
... using this configuration:
To configure the Value Mapper tile,
-
Click the Value Mapper tile in the canvas.
-
(Optional) Rename the tile by clicking
, then entering the name you want.
-
Select the column you want to search.
-
Select whether the values overwrite the values in the specified column or appear in a new column.
-
(Conditional) If writing values to a new column, enter the name of the column.
-
-
Select whether to write the original value or a default value when a match is not found in a row.
-
(Conditional) If writing a default value, enter the value.
-
-
For each value mapping you want, enter the value to search for and the value to replace with.
-
(Conditional) If you want to add a mapping, then click Add Mapping.
Comments
0 comments
Please sign in to leave a comment.