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. Youll notice that even if you do it correctly, there will be some errors (N/A values).

Question 1: Which customer doesnt 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