New Magic ETL Tiles: Utility

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,

1. Click the Add Constants tile in the canvas.

2. (Optional) Rename the tile by clicking , then entering the name you want.

3. Enter the name of the new column.

4. 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.

5. 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,

1. Click the Add Formula tile in the canvas.
2. (Optional) Rename the tile by clicking , then entering the name you want.
3. Enter the name of the new column.
4. 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,

1. Click the Alter Columns tile in the canvas.

2. (Optional) Rename the tile by clicking , then entering the name you want.

3. For each column where you want to set the data type, do the following:

   1. Select the column you want to set.
      For example, "Order Date String".

   2. 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.

   3. (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.

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,

1. Click the Select Columns tile in the canvas.

2. (Optional) Rename the tile by clicking , then entering the name you want.

3. Select the columns you want to include (or exclude) by doing any of the following:

   1. Click Add Column, select a column you want. Repeat to add other columns.

   2. Click Add All Columns, then exclude columns by clicking the X associated with the column.
      For example, remove "Order Quarter".

4. Reorder columns by clicking  and dragging a column to the order position you want.
   For example, moving "Order Date" after "Product Container".

5. 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.

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,

1. Ensure that the column with values you want exists in the DataSet.

2. Click the Set Column Value tile in the canvas.

3. (Optional) Rename the tile by clicking , then entering the name you want.

4. For each column with values you want to replace, do the following:

   1. Select the column with values you want to replace.
      For example, "Product Base Margin". 

   2. Select the column with the values you want.
      For example, "Net Margin". 

   3. (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,

1. Click the Value Mapper tile in the canvas.

2. (Optional) Rename the tile by clicking , then entering the name you want.

3. Select the column you want to search.

4. Select whether the values overwrite the values in the specified column or appear in a new column.

   1. (Conditional) If writing values to a new column, enter the name of the column.

5. Select whether to write the original value or a default value when a match is not found in a row.

   1. (Conditional) If writing a default value, enter the value.

6. For each value mapping you want, enter the value to search for and the value to replace with.

7. (Conditional) If you want to add a mapping, then click Add Mapping.