Adjusting table formatting

The Adjustment action allows you to correct inconsistencies in data by performing formatting adjustments on one or more columns. You can:

• Perform multiple operations on one or more columns in a single action
• Apply a filter to specify which rows will be affected

On the Configuration tab:

1. Drag table fields to define filter and fields for adjustment.

2. Define filter to limit adjusted records.

3. Hover to enable Add icon for a new function.

4. Click the function symbol to choose and define an operation.

5. Click the edit symbol to define the function parameters, if necessary.

6. Use the icons to change the adjustment order or remove the adjustment.

7. Preview data and SQL statement.

Possible applications include:

• Cleanse data from various sources to make it consistent
• Remove leading/trailing spaces
• Remove/replace special characters

Adjust table formatting

1. Drag the Adjustment icon onto the design surface.
2. On the Connections tab , enter a Name and a Description to identify the action, then specify the target table:
   • Database Connection – Select the connection to the database containing the table, or choose New Database Connection to establish a new connection to another database containing the table.
   • Table Name – Select the table in which to perform the adjustments.
3. On the Configuration tab :
   • Drag the icon of a field being adjusted from Table Fields into the Fields area on the right. If more than one field is being adjusted using the same function(s), drag the additional field(s) into the same Fields area.
   • Hover in the Functions area next to the Fields area to enable the Add icon , then click the icon to add a new adjustment operation for the selected field(s).
   • Click the function symbol in the newly added operation to choose the function to be performed, then click Apply.

     

     • Left - Return the left part of a string with a specified number of characters.
     • Right - Return the right part of a string with a specified number of characters.
     • Round Number - Round a number to a specified number of decimal places.
     • Substring - Return part of a string, starting at a specified position.
     • Replace - Replace a string value with another string value.
     • Remove Characters - Remove characters from a string, and optionally specify a replacement character for those being removed.
     • Trim Full - Remove whitespace (blank) characters at the beginning and end of a string.
     • Trim Left - Remove whitespace (blank) characters at the beginning of a string.
     • Trim Right - Remove whitespace (blank) characters at the end of a string.
     • Substring - Return part of a string, starting at a specified position.
     • To Lower Case - Convert uppercase characters to lowercase characters.
     • To Upper Case - Convert lowercase characters to uppercase characters.
     • Pad Left - Pad a string to a specific length, using a specified leading character.
     • Pad Right - Pad a string to a specific length, using a specified trailing character.
   • If the function requires any parameters to be set, click the edit icon for the function to define the parameters.

     

   • Use the icons to the right of the Fields area to change the order in which the adjustments are made, or to remove an adjusment from the action.
   • To more narrowly define the conditions by which the action is executed, open the Filter area and build the necessary expression by dragging icons from the Table Fields area or by using the Expression Editor.
   • Click Preview to view the data or the SQL statement created from this action. Refer to Preview for information.

Adjustment data example

In the following example:

• For PostalCode, pad left with zeroes.
• For DC_Name and SAP_Loc_Code, trim white space and convert to uppercase.