Introduction

Merges can be used to consolidate records from two or more tables. In this page we will work through a simple merge scenario to explain how merges work. All resource used in this example are available here: 

• Businesses1.xlsx
• Businesses2.xlsx

Scenario

We have two sets of company information, Businesses 1.xlsx and Businesses 2.xlsx, that we want to merge together and remove duplicates. There are a number of challenges we must resolve to achieve this:

1. The attribute names in each file are different.
2. There are duplicate companies that exist in both files.
3. There are duplicates within the Businesses 1.xlsx file.

Solution

1. Import the data
   1. Using a file collector import each of the Busiesss Excel documents into their own table and run analysis on each to populate them with data.
   2. Make both of the tables static using the Static option from the table hover over toolbar.
      1. This ensures we only read the files once.
2.  Create the Merge
   1. From the Create section of the analysis toolbar, click  Show Tables
   2. drag the  Merge Table  onto your model.
   3. In the settings, set the Name for example Businesses Merged.
   4. Click  OK to save and close the settings for the merge table.
3. Connect the Inputs
   1. In the analysis model, hover over the Businesses 1 table.
   2. In the hover over toolbar, select  Connector, then click the end of the arrow onto the merge table. This connector will pipe data into the merge table.
   3. In the properties window that opens for the pipe in, set the name to B1 to better indicate the use of the pipe. It is good practice to utilise meaningful names.
   4. In the Sort/Group section click  Show Attributes and PhixFlow displays the list of attributes (columns).
   5. Drag
   6. Similarly, add a connector  from the table Businesses 2 to the merge.

Configure groupings on the pipes

1. Double-click on the pipe from CustomerAddresses  to Customer All Details to open its settings.
   1. In the Basic Settings section, set:
      • Name: this pipe is automatically named in.

   2. In the Sort/Group section click  Show Attributes. PhixFlow displays the list of attributes (columns).

   3. Drag CustomerRef from the list into the settings.
   4. Close the list of attributes.
2. Double-click on the pipe from CustomerAddresses to SOURCE_CUSTOMER_PHONE_NUMBERS. This pipe will be called in_2.

   1. In the Sort/Group section, click  Show Attributes. PhixFlow displays a list of the attributes (columns) in the CustomerAddresses  data.

   2. Drag CUSTOMER_REF into the Sort/Group section.
   3. Close the list of attributes.
3. Now add attributes from CustomerAddresses to the merge stream:
   1. Hover over CustomerAddresses and in the pop-up toolbar, click  Show Attributes.

   2. Drag all attributes from this list and drop them onto the stream icon for Customer All Details.

      How to select all the attributes in a list

      To select all attributes, you can:

      • either click the first attribute then Shift+click the last attribute
      • or click the check box next to the Name header. 

      

   3. PhixFlow asks for confirmation. If it is not already selected, select Use original attribute name. Then click the arrow  to confirm.
      
      
4. Similarly, drag all attributes from SOURCE_CUSTOMER_PHONE_NUMBERS to Customer All Details.
5. To see the resulting settings for Customer All Details, double-click its icon to open the settings tab and look at the Attributes section. You can see a list of 8 attributes (data columns).

Run analysis and view the stream data

1. To run analysis on your new stream, hover your mouse pointer over the stream Customer All Details, and in the pop-up toolbar, click  Run Analysis.
2. To view the data you just loaded, hover over the stream and click  Show view. In the drop-down list, select  Default View. You will see the address data you loaded from the file combined with the phone numbers you loaded from the database.