Prep Data Column Operations

Copy

Operation

Data Type

Description

Copy/Copy With Headers

text, date/time, numeric

Copies column values only or column values with headers to the clipboard.

Create Table from Selection

text, date/time, numeric

Creates a new table based on the columns and rows highlighted in an existing table.

 

filter

Operation

Data Type

Description

Define Filter

text, number, date/time

Launches the Define Filter dialog to create a new filter to apply to the table.

Discard All Filters

text, number, date/time

Removes all filters applied to the table

 

SORT

Operation

Data Type

Description

Sort Selected Column

text, number, date/time

Sorts the column values in descending order

Define Sort

text, number, date/time

Launches the Sort dialog to define a new sort order

 

COLUMN

Operation

Data Type

Description

New Formula Column

text, number, date/time

Adds a new formula column to the table

New Constant-value Column

text, number, date/time

Adds a new constant-value column to the table

New Lookup Column

text, number, date/time

Adds a new lookup column to the table

New Row Number Column

text, number, date/time

Adds a new row-number column to the table

New Runtime Parameter Column

text, number, date/time

Adds a new runtime parameter column to the table

Add or Remove Metadata Columns

text, number, date/time

Adds metadata columns to your table

Rename...

text, number, date/time

Changes the column name.

Duplicate

text, number, date/time

Creates a copy of a column.

Hide

text, number, date/time

Hides the selected column

Delete

text, number, date/time

Removes the selected column from the table.

Show Hidden Column

text, number, date/time

(Displays only when a hidden column exists in a table) Shows a list of hidden columns. Selecting a column from this list displays it in the table once more.

 

CASE

Operation

Data Type

Description

Make Proper Case

text

Converts to uppercase the first letter of each word in a string.

Make Lower Case

text

Converts all uppercase letters in a string to lowercase letters.

Make Upper Case

text

Converts a string to all uppercase letters.

 

CLEAN

Operation

Data Type

Description

Remove Leading & Trailing Spaces

text

Trims all leading and trailing spaces from a string.

Remove Consecutive Spaces

text

Trims to a single space any sequence of two or more consecutive spaces within a string. It also removes any leading or trailing spaces from the string.

Remove Specified Characters

text

Removes a sequence of characters from the values of the column you apply this operation to.

Remove Non Alphanumeric Characters

text

Removes all characters that are not numbers (0-9) or letters of the alphabet (A-Z/a-z) from a column value.

Remove Numeric Characters

text

Removes all numbers from a column value.

Remove Alpha Characters

text

Removes all alpha characters from a column

 

CONVERT

Operation

Data Type

Description

Text to Number

text

Converts a text string to a numeric field.

Advanced Text to Number

text

Converts text fields with the values “X B,” “X M,” and “X K,” where “X” is any number, into “X,000,000,000,” “X,000,000,” and “X,000,” respectively.

Text to Date/Time

text

Converts a string to a date/time.

The date format will be: MM/DD/YYYY

HH:MM:SS to Number of Seconds

date/time

Converts a text string containing time information (in HH:MM:SS format) to number of seconds.

Date/Time to Text

date/time

Converts a date/time value to text.

Date/Time to Number

date/time

Converts a date/time value to number.

Number to Text

number

Converts a numeric field to text.

Number to Date/Time

number

Converts a numeric field to text.

 

EXTRACT

Operation

Data Type

Description

Starting from Left...

text

Extracts n number of characters from the beginning of a string.

Starting from Right...

text

Extracts n number of characters from the end of a string.

Using Position & Length

text

Extracts n number of characters starting from a position you specify.

Between Start & End Strings

text

Extracts n number of characters starting from a start and end position indicated by a sequence of characters.

 

replace

Operation

Data Type

Description

Ditto

text

Replace empty cells with the value of the first non-empty cell above it.

Nulls...

text

Replace  null values with one more characters.

Blank Values...

text

Replace  blank values with one more characters.

Using Find and Replace

text

Substitute a fixed sequence of characters with a different one.

Using Position and Length

text

Substitute a number of characters with a different set of characters starting from a set position.

 

split

Operation

Data Type

Description

Into Parts from Left...

text

Starts from the left and splits a column into two more columns, depending on a separator you define. The separator may be a single character or a substring.

Into Parts from Right...

text

Starts from the right and splits a column into two more columns, depending on a separator you define. The separator may be a single character or a substring.

Names into Parts

text

Splits the value of a single name column into it's component name parts:

  • Prefix

  • First Name

  • Middle Name

  • Last Name

  • Last Name

  • Suffix

Addresses into Parts

text

Splits the value of a single address column into its component address or postal parts.

When you apply this transformation, Prep Data will ask you which of the following name components are available:

  • City

  • State

  • Postal Code

Date/Time into Parts

date/time

Splits a date/time field into two or columns according to the date/time components you select.

Date Time into ISO 8601 Parts

date/time

Splits a date/time field into two or columns according to the ISO 8601 date/time components you select.

Date/Time into Fiscal Parts

date/time

Splits a date field into its components fiscal parts.

 

Statistics

Operation

Data Type

Description

Show Statistics

text, date/time, numeric

Displays statistics about a column. Statistics will show you:

  • If your table has enough rows for you to work with

  • If There are nulls or blanks

  • The maximum, minimum, and average length of the values of the column

Create Statistics Snapshot

text, date/time, numeric

Allows you to create a new table including statistics of a row or several rows.

 

compute

Operation

Data Type

Description

Round with Precision

numeric

Rounds number to a specified number of places to the right (or left) of the decimal point.

Round Up (Ceiling)

numeric

Rounds a number up to the nearest integer.

Round Down (Floor)

numeric

Rounds a number down to the nearest integer.

Round Negative Signs (Abs)

numeric

Returns the absolute value of the value of a column.

Remove Decimals (Int)

numeric

Returns the Integer portion of a number.

Calculator

numeric

Launches the Calculator dialog, which you can use to create a calculated field via a graphical interface

 

OTHERS

Operation

Data Type

Description

Column List

text, date/time, numeric

Allows you to:

Format (date/time)

date/time

Formats date/time columns

Format (numeric)

numeric

Formats numeric columns

Insert Text

text

Inserts a constant text string at a specific location in a text field.

Merge

text, date/time, numeric

Allows you to merge two columns into one.

Redact

text, date/time, numeric

Allows you to redact the values of a column.

Show Statistics

text, date/time, numeric

Displays statistics about a column. Statistics will show you:

  • If your table has enough rows for you to work with

  • If There are nulls or blanks

  • The maximum, minimum, and average length of the values of the column

Create Statistics Snapshot

text, date/time, numeric

Allows you to create a new table including statistics of a row or several rows.

Macros

text, date/time, numeric

Allows you to add or modify macros and apply the same to columns.

Delete Column

text, number, date/time

Allows you to remove a column from the table.

 

 

© 2024 Altair Engineering Inc. All Rights Reserved.

Intellectual Property Rights Notice | Technical Support