Now that we have clean, filtered data coming in from our three sources, we can merge the data to create new data sets, enriching our transaction data by adding the order and product information to the data set.
Merge transactions and orders
We will start by merging the Transactions data with the Order Info data, enriching the list of transactions with cost and pricing information. To merge the data we need a common field to use as the basis of the merge - in this case, both data sets contain a "Product" field, so we'll use that.
- Following on from the previous steps in Preparing data, locate the Lookup node in the Nodes panel and drop it into your data flow. Change the Name property to "Merge Transactions with Orders".
- Connect the Trim Fields node to the data pin of the Lookup node.
- Connect the data pin of the Sort node to the lookup pin of the Lookup node.
- Select the Lookup node, then from the Properties panel, select "Product" for both the Left Field and Right Field in the Match Keys grid.
- You cannot have two output fields with the same name, so we need to exclude one of them from our output. Find the "Product" field in the lookup Fields list in the Field List grid, and untick the box next to it.
- Make sure the node is selected, and click Run.
- Click the record count of the output data set to view it in the data viewer. Each transaction is now listed with cost and order buy information.
Merge with product info
We will now merge this new data set with the Product Info data set, to further enrich the data set with pricing, stock and shipment information.
- Drag a second Lookup node onto the canvas and connect the LookupOutput pin of the "Merge Transactions with Orders" node to its data input pin, then connect the output from the "Shipping Cycle 14" node to the lookup input pin.
- From the Properties panel, change the Name of the node to "Merge with Product Info".
- In the Match Keys grid, select "SupplierID" in both the Left Field and Right Field columns on the first row, then select "Product" for both Left Field and Right Field on the next row.
- As with the previous merge, you now need to exclude these fields from the output data set. Exclude the "SupplierID" and "Product" fields from the lookup Fields list in the Field List grid.
- Run the node.
- Click the output pin record count to view the output data set. You might need to scroll to the right to view the new columns in the data viewer.
- Save your latest changes to the data flow.
Next steps
You now have all the data in one place to allow you to figure out total sales and orders. It's time to summarize, aggregate, and then publish the data, see Summarizing data.