Database Systems Management > QUESTIONS & ANSWERS > Arizona State University - CIS 360CIS 360 Assignment 6. CIS 360: Homework Assignment 6 Assignment: E (All)

Arizona State University - CIS 360CIS 360 Assignment 6. CIS 360: Homework Assignment 6 Assignment: ETL and Excel Functions.

Document Content and Description Below

CIS 360: Homework Assignment 6 Assignment: ETL and Excel Functions Name: ______ Part 1: • Work with the excel file (file name: ExcelFunctionsWorksheetCIS360.xlsx) and walk through the steps in the Excel functions tutorial (file name: ExcelFunctions.pdf). • Screenshot at least 5 steps in the process. Put the 5 screenshots into a Word file as “ExcelFunctions_YourLastName.docx”. o For example, take a screen shot of the entire Excel interface and specify which step the screenshot is taken after. • After you walk through the steps, save the changed Excel file to your computer as “ExcelFunctions_YourLastName.xlsx”. • Uploaded both the Excel file and the Word file with screenshots to Blackboard by Assignment due date. Part 2: • 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). • Submission Guidelines 1) You must submit (1) the answers to the questions in this document, and (2) the final version of your “ETL Exercise.xlsx” worksheet. 2) You must include your name at the top of the document. And name the excel form as “ETL – your name.xlsx”. 3) Your answers should be uploaded, as an attachment, to your blackboard assignment. • Evaluation - your submission will be graded based on two factors: 1) The correctness of the answers to the questions. 2) The accuracy of the “Full Set” worksheet in the “ETL Exercise.xlsx” workbook. Problem A: Credit Line field Question 1: Which customer doesn’t have data when you apply the VLOOKUP() function? Max kindle did not have data. 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. Problem B: 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). The criteria I used to transform the data was that if a Value in the Missed payments column was equal to “None”, then it would be replaced with the number 0, but if it did not match (aka 1,2,3,etc), then the number would stay the same. Problem C: 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). The data transformation rule for the country is that if the data in the country column starts with the letter “U”, then it would return as “USA”. But if it was deemed false (aka does not start with U), then it would return as “Canada”. Looking at the IF() function: Using the IF() function with OR(): •

[Show More]

Last updated: 3 years ago

Preview 1 out of 4 pages

Buy Now

Instant download