Assignment 5: ETLDelivery: You will need to submit the following two things:(1) Please submit a hard copy of your answer sheet with answers in the last page of this document (just the answer sheet).(2) Pease also submit your final version of the excel worksheet to blackboard. This assignment builds on the ETL In-Class Assignment using the Excel workbook “ETL Exercise.xlsx.” If you hav
...[Show More]
Assignment 5: ETL
Delivery: You will need to submit the following two things:
(1) Please submit a hard copy of your answer sheet with answers in the last page of this document (just the answer sheet).
(2) Pease also submit your final version of the excel worksheet to blackboard.
This assignment builds on the ETL In-Class Assignment using the Excel workbook “ETL Exercise.xlsx.” If you haven’t finished that yet, you’ll need to do that before starting this.
The In-Class Exercise involved a scenario where you brought together two different data sets from two sources. Each data set contained a group of orders by a group of customers, and those customers did not overlap (no customer was in both data sets).
For this assignment, you’ll be building on that data set by adding new fields to the “Full Set” worksheet. Instead of adding new rows, this time you’ll be adding new columns. The data will come from the “Source 3” worksheet (also in the workbook).
Part 1: Credit Line field
Add the data for credit line to the “Full Set” worksheet. A minimum credit line of $2,000 has been established, so that even if the customer has a credit line of $0 it is changed to $2,000. Use the VLOOKUP() function to put this data into the “Full Set” worksheet. You’ll notice that even if you do it correctly, there will be some errors (“N/A” values).
Question 1: Which customer doesn’t have data when you apply the VLOOKUP() function?
Question 2: Explain why this is causing a problem.
Now make the necessary change to the Source 3 worksheet to correct the issue so that Credit Line data appears for all the customers.
[Show Less]