Transformed value

<< Click to Display Table of Contents >>

ETL > 14.0 u1 > Implementation Guide > Tutorial and implementation guide > Steps > Mapping 

Transformed value

The value can be transformed using a built-in transform. The following transformation are available:

Transform name

Behavior

Lowercase

Returns lowercase variant of the value. This transformation by default preserves the original data type.

Uppercase

Returns uppercase variant of the value. This transformation by default preserves the original data type.

IsNullOrEmpty

Returns 0 is the value is not null or empty or 1 otherwise. This transformation maps by default to a boolean value.

IsNotNullOrEmpty

Returns 1 is the value is not null or empty or 0 otherwise. This transformation maps by default to a boolean value.

Length

Returns the length of the string value. This transformation maps by default to a 32-bit integer value.

MD5

Hashes the given column with MD5 hash. Returns 128-bit (16 bytes) hexadecimal string of 32 characters.
 
Note: This transformation is available in ETL module version 1.1.271 and newer.

SHA1

Hashes the given column with SHA1 hash. Returns 160-bit (20 bytes) hexadecimal string of 40 characters.
 
Note: This transformation is available in ETL module version 1.1.271 and newer.

There are three ways of defining transformation:

Simple definition:

{

   "columns":
   {

       "target_column_name": "source_column_name-><type>"

   }

}

Object definition:

{

   "columns":
   {

       "target_column_name":

       {

           "name": "source_column_name",
          "transform": "<type>"

       }

   }

}

 

Full object definition

{

   "columns":
   {

       "target_column_name":

       {

           "name": "source_column_name",
          "transform":
          {
              "type": "<type>"

          }

       }

   }

}

 

 
Where <type> is one of the supported value: LowercaseUppercaseIsNullOrEmptyIsNotNullOrEmpty or Length. Transformation names are case-insensitive. All three ways of defining a transformation are functionally and technically equal. We recommend using the simplified string notation, as it is the least verbose of all three.

Example

Given the following table SourceTable:

Name

Surname

Age

Marcin

Otorowski

34

John

<null>

51

Kate

Binks

25

 
And the following JSON step definition:

    {

      "id": 1,

      "type""map",

      "name""Example of transform-columns",

      "source""SourceTable",

      "columns": {

        "Name""Name",

        "Name_Uppercase""Name->uppercase",

        "Surname_IsProvided""Surname->isnotnullorempty",

        "Surname_Length""Surname->length"

      },

      "target""TargetTable_TransformedColumns"

    }

 

The following result table TargetTable_TransformedColumns is expected:

Name

Name_Uppercase

Surname_IsProvided

Surname_Length

Marcin

MARCIN

1 (bit)

9

John

JOHN

0 (bit)

0

Kate

KATE

1 (bit)

5

 

Arbitrary types, casting and converting

Using the type property lets you define a custom cast or custom type length. This common property is available for all column types, see more information in chapter Arbitrary column types.

Fallback values

It is possible to define a custom value returned, if the source column does not exist in the table. For more information about the usage of fallback values, see the Fallback columns chapter.