Actions on the Column

You can perform various actions to alter values in a specific column within a worksheet.

When you select a column header, a drop-down menu appears with a list of actions you can perform on the selected column of data.

Table 1: Data Actions
Action	Description	Example
Change Case	Change the text to upper case or lower case	Select Lower Case for a column that says `USA` to change it to `usa`.
Trim	Remove the specified character from the beginning, end, or both sides of the string. Specify one of the following characters to remove: Space Comma Colon Semi-colon	If a column says `USA:` and you choose to trim the colon from the end, the string will be `USA`.
Replace	Replace every occurrence of a specified string or character with another string. Specify one of the following strings or characters: Custom string Space Tab Return Null Note If you select `Tab` or `Return` as the string to be replaced in a column, SAP Agile Data Preparation does not recognize and highlight tabs or spaces that occur at the beginning or end of a column value.	You can replace every occurrence of `USA` with `United States of America`. You can also replace all occurrences of a substring within a string. For example, you can replace all instances of `a` with `A`, so that `United States of America` becomes `United StAtes of AmericA`.
Fill	Pad the beginning or end of the string with the specified value until the desired length is reached.	If you want all last names to have a length of 30, you can enter a space as the padding string and `30` for the desired length.
Harmonize Values	Harmonize several values into one by selecting the values you want to merge, typing in the value you want to replace them with, and clicking Apply. Note If you harmonize values for a date column and type in four digits as the replacement value, the four digits will become the year with the month and day set to 1. For example, if you type `1234` as the replacement value, the selected dates become 1234-01-01.	You can harmonize the values `US`, `USA`, and `United States` into `United States of America`.
Clear	Clear all the data from a column without deleting the column.	If you do not want data to appear in a column of a worksheet you will share, you can clear all the values in a column.

Table 2: Column Actions

Action

Description

Example

Combine

Combine values in the selected column with one or more columns and specify one of the following strings between both values:

Custom string
Space
Tab
Return

See Combine for more information.

You can combine the First Name values with the Last Name values and specify a space between them.

Split

Split values into two or more columns. Specify one of the following values on which to split the string:

Custom string
Space
Tab
Return

See Split for more information.

You can split Fullname values into two or more columns, and then rename the generated names Fullname1 to First Name and Fullname2 to Last Name.

If there are multiple occurrences of the split string, it will split on the first instance and leave the second instance together. For example, if a column with the full name has the text Mary Ann Smith, the split would produce two columns with the values Mary and Ann Smith.

Convert to Number

Convert string values to either integer or decimal values.

Note

If you want to deal with possible invalid numbers in the column data, change the Allowed Exceptions value from 0 to the number of invalid values you are willing to ignore, and enter a valid numeric value in Resolve with for all invalid rows.

You can convert a numeric customer column that was uploaded as string values to integer values.

Format Number

Apply formatting changes to all the values of a numeric column. You can add a prefix, add a suffix, identify how many digits appear after a decimal, and add grouping or decimal separators. This option is available only if your column is of a number type.

Note

This formatting will appear only on your local worksheet and will not be saved if the worksheet is shared or exported.

You can add a prefix of $ for a price column. You can also use a grouping separator of , to change values such as 1000 to 1,000.

Convert to Date

Convert string values to date values in the selected format.

Note

If you have any of the following invalid values in a column, select the Handle Exception checkbox. Enter a valid date that you want to convert the invalid value to in the Fallback Value field and enter the number of times the invalid value occurs in the Threshold field.

Values that are not in the correct date format
Values with additional information, such as commas.

Note

If the column contains invalid values, such as an incorrect number of digits, the convert to date is performed but the results contain invalid results. For example, if a column contains a string “123”, the convert to date with format yyyy-mm-dd results in the value “0123-01-01”.

Suppose your worksheet has a date column that contains string values such as 1958-03-19 and 1972-12-14. You can convert these values to dates with the format YYYY-MM-DD.

Format Date

Apply formatting changes to all the values of a date column. You can choose from one of four date formats:

2/12/16
Feb 12, 2016
February 12, 2016
Friday, February 12, 2016

This option is available only if your column is of a date type.

Note

This formatting will appear only on your local worksheet and will not be saved if the worksheet is shared or exported.

You can change date values from a format such as December 1, 1999 to 12/1/1999.

Format Date and Time

Apply formatting changes to all the values of a datetime column. You can choose from one of four formats:

December 24, 2016 10:00 PM
Dec 24, 2016 10:00 PM
Saturday, December 24, 2016 10:00 PM
Sat, Dec 24, 2016 10:00 PM

You also have the option to change the Coordinated Universal Time (UTC) to a timezone of your choice.

This option is available only if your column is of a datetime type.

Note

This formatting will appear only on your local worksheet and will not be saved if the worksheet is shared or exported.

You can change date values from a format such as December 1, 2016 10:00 PM to Sat, Dec 1, 2016 10:00 PM.

Format Time

Apply formatting changes to all the values of a time column. You can choose from one of four formats:

15:35:30
15:35:30.123
03:35:30 PM
03:35:30.123 PM

This option is available only if your column is of a time type.

Note

This formatting will appear only on your local worksheet and will not be saved if the worksheet is shared or exported.

You can change date values from a format such as 03:35:30 PM to 15:35:30.

Clear Format

Clear formatting changes that were applied to a column of type numeric, date, datetime, or time. This option is available only if you had previously applied Format Number, Format Date, Format Time, Format Date and Time, or Format Date.

For date, datetime, and time columns, the default format applied during worksheet creation is cleared and the data will display in ISO8601 format as the following table shows.

Agile Data Preparation Data Type	ISO8601 Format
DATE	2014-02-15
TIME	15:34:10.000
DATETIME	2014-02-15T15:34:10.000Z

Suppose your worksheet has a date column with the format 6/19/2016, and you change the format to June 19, 2016. If you clear the formatting, the date column is now formatted as 2016-06-19.

Duplicate

Duplicate the values in the selected column and specify a name for the new column.

You can create a second column titled Country 2 that duplicates the data from the Country column.

Rename

Change the column name to the specified value.

You can change the column name from Country to Nation.

Remove

Delete the column from the worksheet.

You can delete the column titled Country entirely from the worksheet.