By the end of this chapter you will be able to:

• Use the attribute function replaceAll()
• Use the attribute function toUpper()
• Use the attribute function substring()
• Use the attribute function if()
• Use the internal variable _out

Reformat post codes to remove all spaces and make upper case

You will reformat post codes to remove all spaces, and ensure that all letters are upper case:

1. In your model, add a new table:
   1. Hover over Customer Details with Region, and press  Create New Table.
   2. Give the new table the Name: Reformatted Customer Details
   3. Drag all attributes from Customer Details with Region to the new table.
2. Open the configuration form for Reformatted Customer Details
   
   1. In the Attributes section, add a new attribute:
      • Press  Add New
      • In the attribute details form that pops up:
      • Set the Name: ReformattedPostCode
      • Leave the settings for the attribute as default (Type is String, Length is 50)
      • Set the Attribute Expression to

toUpper(replaceAll(in.PostCode, " ", ""))

• • Press  OK.

Above the Attribute Expression the help link will open a page containing details of all attribute functions available in PhixFlow. Find the attribute functions you used above – toUpper() and replaceAll() –  click each of these to see the help pages specifically for these functions.

Inline help is also provided for functions, this will appear as you type. At the bottom of the popup help is a link directly to the page for the function in question.

Generate short code for Counties

Now you will generate a short code for counties by taking the first 3 letters of the county for each customer detail record:

1. Add a new attribute called CountyShortCode.
2. Leave the default settings.

3. Set the attribute expression to:

   substring(in.County, 1, 3)

4. Press  OK.

Categorise bills as "LARGE" or "NORMAL"

Categorise bills as "LARGE" or "NORMAL":

1. Add a new attribute called BillCategory
2. Leave the default settings.

3. Set the attribute expression to:

   if (in.BILLAMOUNT > 10.0, "LARGE", "NORMAL")

4. Press  OK.

Set special processing flag _out

Finally, you will now set a special processing flag – this will be set to "YES" when the last bill for the customer is large. To do this, we need to access an attribute value in the table, but which is not in any of the input pipes to the table - only in the table output. To do this, you will use the internal variable _out:

1. Add a new attribute called SpecialProcessing
2. Leave the default settings.

3. set the attribute expression to:

   if (_out.BillCategory == "LARGE", "YES", "NO")

4. Press  OK.

Run table

1. In the main table configuration form, press  OK.
2. Remember to save your model layout,  Save.
3. Run analysis on Reformatted Customer Details
4. Have a look at the data in the new table – check that all your transformations have been correctly applied.