View Source

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']

Token	Required?	Data Type	Description
merge	Y	transform	Name of the transform
col	Y	string	Source column name or names
with	N	string	String literal used in the new column as a separator between the merged column values
as	N	string	Name 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
Yes	String (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
No	String (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
No	String (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:

OrderId	Month	Day	Year
1001	2	14	2008
1002	7	22	2009
1003	11	22	2010
1004	12	25	2011

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.

OrderId	Month	Day	Year	Date
1001	2	14	2008	2/14/2008
1002	7	22	2009	7/22/2009
1003	11	22	2010	11/22/2010
1004	12	25	2011	12/25/2011

col

with

as

Example - Merging date values

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