Joining data from multiple sources and Aggregating
From now on we will use videos as a good way of showing the other scenarios. You should have a good grasp of things by now. A short summary will accompany each scenario. Try to build this yourself using the brief given below.
We have been tasked with providing a data file that shows Total Sales, Average Sale Value and Total Number of Sales for each Customer. We have 3 input files (they should already be loaded into the "data-in" folder).
|•||customers.csv : we know this one already|
|•||orders.csv : each record is a single order and contains the "OrderID" key field|
|•||orderdetails.csv : an Order is made up of one or more line items. Each line item has its own record in the orderdetails,csv file.|
See if you can come up with a transformation that does this. Think about your Metadata and define your own "User Defined" metadata item that contains only the four required fields. Be sure to specify the Join property. You will also need to multiply fields together to generate the total value of an order. The video touched on this.
I strongly recommend that you try your hand at building this graph without loading the pre-created graph below. The reason for this is that you should now look to engage in your first skirmish. If you lose this skirmish then load the graph from the XML below, but make sure you have really lost first.
Also, there are several ways to solve the same problem. Don't worry if your graph is not identical to mine. Main thing is that it works.