Settype Transform

Identifies the column(s) to which to apply the transform. You can specify one or more columns.

Usage Notes:

Defines the data type that is to be applied to the transform. Type is defined as a String literal.

For a list of valid strings, see Valid Data Type Strings.

settype col: zips type:'Zipcode'

Output: Changes the data type of the zips column to Zip Code data type. All values are validated as U.S. Zip code.

Usage Notes:

Source:

Here is a list of activities listed by date. Note the variation in date values, including what is clearly an invalid date. Here is the source data:

myDate, myAction
4/4/2016,Woke up at 6:30
4-4-2016,Got ready
9-9-9999,Drove kids to school
4-4-2016, Commuted to work

Transformation:

When this data is imported into the Transformer page, there are couple of immediate issues: no column headings and blank rows at the bottom. These two transformations fix that:

Use row(s) as column names

Use a single row to name columns

1

Custom formula

Custom single

ismissing([myDate])

Delete matching rows

For the invalid date, you can infer from the rows around it that it should be from the same date. You can make the following change to fix it:

myDate

`9-9-9999`

'4-4-2016'

true

Now that the dates look fairly consistent, you can set the data type of the column to a matching Datetime format:

myDate

Custom or Date/Time

'mm-dd-yy','mm*dd*yyyy'

Note the syntax above for specifying Datetime types. In addition to the Datetime keyword, you must specify the format type, followed by the variation of that format.

Results:

This example illustrates how to clean up data by changing its data type to String, manipulating it using String functions, and then retyping the data to its proper data type.

Functions:

Source:

The following example contains customer ID and Zip code information in two columns. When this data is loaded into the Transformer page, it is initially interpreted as numeric, since it contains all numerals.

The four-digit ZipCode values should have five digits, with a 0 in front.

Transformation:

CustId column: This column needs to be retyped as String values. You can set the column data type to String through the column drop-down, which is rendered as the following transformation:

CustId

String

While the column is now of String type, future transformations might cause it to be re-inferred as Integer values. To protect against this possibility, you might want to add a marker at the front of the string. This marker should be removed prior to execution.

The basic method is to create a new column containing the customer ID marker (C) and then merge this column and the existing CustId column together. It's useful to add such an indicator to the front in case the customer identifier is a numeric value that could be confused with other numeric values. Also, this merge step forces the value to be interpreted as a String value, which is more appropriate for an identifier.

'C',CustId

You can now delete the CustId columns and rename the new column as CustId.

ZipCode column: This column needs to be converted to valid Zip Code values. For ease of use, this column should be of type String:

ZipCode

Zipcode

The transformation below changes the value in the ZipCode column if the length of the value is four in any row. The new value is the original value prepended with the numeral 0:

ZipCode

if(len($col) == 4, merge(['0',$col]), $col)

This column might now be re-typed as Zipcode type.

Results:

Remember to remove the C marker from the CustId column. Select the C value in the CustId column and choose the replace transform. You might need to re-type the cleaned data as String data.