Prep Data Column Operations

When preparing data in Monarch Data Prep Studio, you can perform various operations not only for a single column but also for multiple columns of either the same data type or different data types. The availability of these operations depends on the data types of the columns selected. This section lists and defines the operations that you can perform when working with columns in Monarch Data Prep Studio.

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.

Alignment

Operation	Data Type	Description
Left	text, number, date/time	Aligns all values to the left of the column
Center	text, number, date/time	Aligns all values at the center of the column
Right	text, number, date/time	Aligns all values to the right of the column

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.
Line of a Text Block by Line Number	text	Extracts a line from a text block based on the number of the line in the block.
Line of a Text Block by Search Term	text	Extracts a line from a text block based on a given search term.

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
Collapse changes unrelated to this column	text, date/time, numeric	Allows you to collapse all items in the Change History list that are unrelated to the selected column.
Column List	text, date/time, numeric	Allows you to: Rename a column Change column types Hide/unhide columns Reorder columns Add or edit macros Assign column widths and text alignment Set name casing Apply a selected format to all columns of the same type
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.

Multi-column operations

When working on multiple columns, you can perform various operations depending on the data types of the selected columns.

The Duplicate Columns and Alignment operations are available for multiple columns regardless of data type.
The Extract and Split operations are available for multiple columns containing text and date/time data.
The Compute operation is available for multiple columns containing numeric data.

Selecting any of the Extract, Split, and Compute operations launches a corresponding dialog box.

From the dialog box, you can further modify the operation and manage the selected columns by sorting or filtering them.

Intellectual Property Rights Notice | Technical Support