ETL Assignment: I need help with formulas and questions 3 & 4 Part 1: Credit Lin

Question

ETL Assignment: I need help with formulas and questions 3 & 4

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.

Part 2: Missed Payments field

Add the data for the credit line to the “Full Set” worksheet. In the Source 3 worksheet, if a customer has no missed payments, their value for that field is “NONE”.

First, transform the data in the Source 3 worksheet by creating a formula for the “Missed Payments 2” column. That column should only have numeric data (you can use the IF() function to do this – use the explanation of the IF() function below and the example in the “New Credit Line” column as a guide).

Once you do the transformation, use the VLOOKUP() function to bring the data in the “Missed Payments 2” column into the “Full Set” worksheet.

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

Add the data for the country field to the “Full Set” worksheet. Notice that the United States is represented several different ways. Choose one, and transform the remaining data so that the value for the United States is consistent across all customers. Use the “Country 2” column to hold the transformed data. Use an IF() statement to transform the data. Then use the VLOOKUP() function to bring the data into the “Full Set” worksheet.

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).

Customer Name Previous Credit Line New Credit Line Missed Payments Missed Payments 2 Country Country 2 Allan Strate $10,000.00 $10,000.00 1 USA Cathrine Delamater $20,000.00 $20,000.00 3 USA Christin Tillinghast $8,000.00 $8,000.00 5 United States Daniela Becknell $10,000.00 $10,000.00 NONE United States Darryl Manuelito $10,000.00 $10,000.00 NONE US Eleanor Aburto $10,000.00 $10,000.00 NONE Canada Elnora Willison $20,000.00 $20,000.00 3 US Erik Vire $0.00 $2,000.00 NONE US Eve Haak $5,000.00 $5,000.00 NONE United States Jamie Sabat $5,000.00 $5,000.00 NONE Canada Jamie Woodbridge $5,000.00 $5,000.00 5 USA Julio Willard $10,000.00 $10,000.00 NONE USA Kisha Grauer $7,500.00 $7,500.00 NONE USA Leota Vonderheide $7,500.00 $7,500.00 3 USA Lonnie Ludy $10,000.00 $10,000.00 2 USA Max Kindel $10,000.00 $10,000.00 NONE USA Milagros Fehrenbach $0.00 $2,000.00 NONE USA Odessa Smith $20,000.00 $20,000.00 NONE USA Roslyn Plott $20,000.00 $20,000.00 NONE US Roxie Prewitt $20,000.00 $20,000.00 3 US Suzan Plock $7,500.00 $7,500.00 NONE US Tyrone Hine $20,000.00 $20,000.00 2 US

Explanation / Answer

3. The IF formula to be used should be = IF(LEFT(B4,1)="U","USA",B4).

Where cell B4 is the country cell. So the logic is that the LEFT formula will give us the first letter of the cell and if the first letter of the Country is from U (US, USA or United states) the answer will be USA or else it will give the same name as in Country.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.