Creating a Transformation
Adding a Transform is accomplished by selecting the
Add Transform button from the
Main Transforms Page. A new window will be created that invokes the Transformation Stepper process.
The first part of the 5 step process is to define the Source. The Source step is responsible for the following:
Transform Name - Enter a descriptive name for the Transform of up to 32 characters.
The Transform Name represents the name that will be applied to the Transform. The name should describe something that relates to the transform.
Transform Source option - Select from Single Table Transform or Free-Form SQL.
The source can be defined with a single table selection or with a SQL Statement. The following table offers advantages of each source to help decide which source is appropriate to move the correct data.
Transform Type | Simple Source Table Transform | Free From SQL |
Selecting Subset of Table or Tables | Yes | Yes |
Translating coded values (e.g. Changing the Source column from a 1 to Male) | Yes | Yes |
Calculated Values (e.g. OrderAmt * 1000) | Yes | Yes |
Filtering | | Yes |
Sorting | | Yes |
Joining Multiple Tables (e.g. Lookups) | | Yes |
Aggregation | | Yes |
Unions | | Yes |
Creating a Single Table Transformation
To create a Single Table transform, select the Use single existing table for the source of the transform radio button. A drop down list box will be displayed under the Select Source Table caption. This list will contain all of the potential tables that are included in the CONNX Data Dictionary File. Select a single table which will serve as the input for the target destination file.
One potential reason to choose this type of transformation might be that you only want to include certain fields of an existing table. It can also be used to map derived columns (e.g. orderamt * 100). When choosing this type of transformation, SQL is not required unless a derived field is created and requires SQL to create a transformation from source column(s).
Designing a Free-Form SQL Transformation
If you want to use a specific SQL query as the Transformation source, select Use a Query to define the source of the transform and then either type or cut/paste from Infonaut the query that you want to use as source of the transformation. If there is a syntax error, it will be caught when you click Next.