WPC300: Problem Solving and Actionable Analytics
Assignment: ETL
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 t
...
WPC300: Problem Solving and Actionable Analytics
Assignment: ETL
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).
Guidelines
• You must submit (1) the answers to the questions on the last page of this document, and (2) the final version of your “ETL Exercise.xlsx” worksheet.
• You must include your name at the top of the document. And name the excel form as “ETL – your name.xlsx”.
If you do not follow these instructions, your assignment will be counted late.
Evaluation
Your submission will be graded based on two factors:
• The correctness of the answers to the questions.
• The accuracy of the “Full Set” worksheet in the “ETL Exercise.xlsx” workbook.
Part 1: Credit Line field
Question 1: Which customer doesn’t have data when you apply the VLOOKUP() function?
Question 2: Explain why this is causing a problem.
Part 2: Missed Payments field
Question 3: Write the data transformation rule for the missed payment field (not the syntax of the IF() function; just explain the criteria you used to transform the data).
Part 3: Country Field
Question 4: Write the data transformation rule for the country field (not the syntax of the IF() function; just explain the criteria you used to transform the data).
Looking at the IF() function:
Using the IF() function with OR():
NAME: __Kevin Durham_____________________________________
Answer Sheet
(enter your answers below and submit this along with the completed Excel workbook)
Question 1: Which customer doesn’t have data when you apply the VLOOKUP() function?
Question 2: Explain why this is causing a problem.
Question 3: Write the data transformation rule for the missed payment field (not the syntax of the IF() function; just explain the criteria you used to transform the data).
Question 4: Write the data transformation rule for the country field (not the syntax of the IF() function; just explain the criteria you used to transform the data).
[Show More]
