Chapter 13: Appending Fields

Chapter 13: Appending Fields Transcript

One of the key features of IDEA is that data is protected once it has been imported. You can't change it, edit it or correct any errors which you may find. What you can do is add new fields to test and validate data.

I want to use IDEA to generate a new credit limit showing a 10% increase and then check to see that no customer has a balance greater than the new credit limit. I'll start by creating a new field for this new limit, then will compare the current balance to this new value.

To start, let's make a new field. Ensure the Customer Balances database is active, data is selected and that any criteria from the previous exercise have been cleared. Click the Data tab in the IDEA ribbon and in the Fields section, select Append. Enter a new name for the field, such as NEW_LIMIT. Accept Virtual Numeric as the field type. Length is not required for a numeric field so skip this step. It's a numeric field so we use two decimal places and in the parameter field, you need to enter the equation to add 10% to the existing credit limit. The equation for this is CREDIT_LIM *1.1.

In the description field, type Updated Credit Limit and click OK. The new field is added to the right side of the data. It is a different color to distinguish it from the data which was imported and is protected.

Now let's compare the current customer balances against that new value to ensure that nobody has a balance which exceeds that number.

In the Analysis Tab, click Direct in the extract group. Call the new database Exceeded Credit Limits or something similar. Click the equation editor button and enter an equation to compare the new fields. Something such as AMOUNT_SUM is greater than NEW_LIMIT. Click validate and exit to return to the direct extraction dialog and click OK to finish running the extraction.

The new database contains 11 records, the 10 entries for which we don't have records in the Customer file so there is no credit limit to compare against, and one other who has actually exceeded her credit limit. When you're finished, close all the open files.