Merges two or more columns in your dataset to create a new column of String type. Optionally, you can insert a delimiter between the merged values. 

NOTE: This transform applies to String columns or other columns that can be interpreted as strings (for example, Zip codes could be interpreted as five-digit strings). To concatenate arrays, use the ARRAYCONCAT function. See ARRAYCONCAT Function.

Column example:

merge col:Column1,Column2 as:'MergedCol'

Output: Merges the contents of Column1 and Column2 in that order into a new column called MergedCol.

Column and string literal example:

merge col:'PID',ProdId with:'-'

Output: Merges the string PID and the values in ProdId together. The string and the value are separated by a dash. Example output value: PID-00123.

merge col:column_ref [with:string_literal_pattern] [as:'new_column_name']

TokenRequired?Data TypeDescription
mergeYtransformName of the transform
colYstringSource column name or names
withNstringString literal used in the new column as a separator between the merged column values
asNstringName of the newly generated column

col

Identifies columns or range of columns as source data for the transform. You must specify multiple columns.

merge col: Prefix,Root,Suffix

Output: Merges the columns Prefix, Root, and Suffix in that order into a new column.

Required?Data Type
YesString (column name)

with

merge col: CustId,ProdId with:'-'

Output: Merges the columns CustId and ProdId into a new column with a dash (-) between the source values in the new column.

Required?Data Type
NoString (column name)

as

merge col: CustId,ProdId with:'-' as:'PrimaryKey'

Output: Merges the columns CustId and ProdId into a new column with a dash (-) between the source values in the new column. New column is named, PrimaryKey.

Required?Data Type
NoString (column name)


Example - Merging date values

You have date information stored in multiple columns. You can merge columns together to form a single date value.

Source:

OrderIdMonthDayYear
10012142008
10027222009
100311222010
100412252011

 

Transformation:

merge col:Month~Year with:'/' as:'Date'

Results:

When you add the transform and move the generated Date column, your dataset should look like the following. Note that the generated column is automatically inferred as Datetime values.

OrderIdMonthDayYearDate
100121420082/14/2008
100272220097/22/2009
10031122201011/22/2010
10041225201112/25/2011

Example - Use merge and settype to clean up numeric data that should be treated as other data types