Merging Columns

You can easily merge columns by highlighting the columns you want to merge and selecting Merge Columns. You can select to do a quick merge or build the merge using the Define Merge dialog.

A merge operation always produces a (merged) column with a text data type.

Merging columns using Quick Merge

Steps:

1. Select the columns you wish to merge.

   

    

   You may select as many columns as you need.

2. Right-click on the highlighted columns select Merge Columns > Merge Selected Columns.

   

    

   The Merge Selected Columns dialog displays:

   

    

3. Enter a split delimiter: a character (or set of characters) that will separate the merged values in the output column. For example, you can use a comma and a space character to separate the two address fields.

4. Tick the Interpret \n as newline and \t as tab checkbox to use these characters (i.e., \n, \t) as advanced delimiters.

5. Tick the Remove original columns checkbox if you wish to remove the original (i.e., unmerged) columns from the resulting table.  

6. Select OK. The selected columns are merged.

   

    

   By default, the new column is named <First column name>MergeText. Rename the Merge output as needed.

Merging columns using the Define Merge dialog

The Define Merge dialog allows you to define a more complicated merge. It also allows you to select additional merge columns on the fly.

Steps:

1. Select the first column you want to merge.

2. Right-click on the highlighted column and then select Merge from the options that display.

   The Define Merge dialog displays.

   

    

3. Build your merge column.

   

    

   • Add Columns to Merge

   • Double-click a column on the Select Column or Text panel to add the column to the Merge Column panel. All columns in the Merge Column panel will make up the new merge column. Values are merged in the order the columns are listed on the Merge Column panel, including any delimiters between each column.

   • Move columns to define the merge order

   • Drag the double bar located to the left of each column to move columns up or down in the Merge Column panel. This will define how values are displayed in the new column.  

   • Delete Columns

   • Use the delete icon at right of each column to remove columns from the  Merge Column panel if you decide you no longer want to include a column in the merge.

   • Add Delimiters

   • Double-click <click add text> on the Select Column or Text panel to add a text box to the Merge Column panel. You can then enter a text delimiter to separate columns.

     Tick the Interpret \n as newline and \t as tab box to use these characters (i.e., \n, \t) as advanced delimiters.

   • Add All Columns

   • Click the Add All Columns button to instruct Monarch Data Prep Studio to add all of the columns in your table to the Merge Column panel, essentially merging all of the columns in your table into a single column.

   • Clear Columns

   • Click the Clear Columns button to remove all columns added to the Merge Column panel.

4. Tick the Remove original columns checkbox if you wish to remove the original (i.e., unmerged) columns from the resulting table.  

5. Click OK when you are finished.