Extracting Blanks, Nulls, and/or White Spaces

The Extract transform option allows you to include or exclude only rows that:

• are empty

• contain null columns

• contain contains only white spaces (blanks)

For instance you start with a table like this:

And remove empty rows to have a table like this:

Extracting Rows with Blanks, Nulls, and/or White Spaces from a Table

You can only remove empty rows from the Prep Data Window. To do so:

1. Go to Prep Data and select the table you want to transform.

2. Select Transform Data on the Data Prep Studio Toolbar.

A dialog box allows you to select a transformation.

4. Select Extract Rows. The Extract Rows dialog box displays:

5. On the New Table Name box, enter a name for the resulting table.

6. From the Extract Operation drop-down, select the exclude or include operation you want to carry out:

• • Exclude all rows where all columns contain: allows you remove all rows where all columns values are blank, null and/or contains spaces.

  • Exclude all rows where any columns  contain:  allows you remove all rows that have at least one column whose value is blank, null and/or contains spaces.

  • Include all rows where any columns contain: allows you include all rows that have at least one column whose value is blank, null and/or contains spaces.

7. Select column criteria. Click one or all of the following:

   • No content: Represents a value that is intentionally left blank.
     For example, the column Spouse Name may be left blank for an individual who is not married.

   • Null: Indicates a value that does not exist at all.

   • Whitespace Only - Represents a value of white spaces (text strings)

8. Select the columns against which the criteria will be evaluated:

• • Use All Columns

  • Use Selected Columns. If you select this option you must select columns from the list.

Click on a key icon beside a column to select it. You can use the Search Column box to easily look for the columns you want to include.

9. Click OK when you are finished.

Data Prep Studio removes or includes records as specified and creates a new table: