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 In your Introduction to Modelling analysis model, you will now 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 

      Insert excerpt
      _table_new _table_new nopanel true

      .
   2. Give the new table the Name: Reformatted Customer Details.
   3. Drag all attributes from  to the new table Customer Details with Region to the new table.
2. Open the configuration form settings for the Reformatted Customer Details table.
   
   1. In the Attributes section, add a new attribute:
      • Press 

        Insert excerpt
        _add _add nopanel true

      • In the attribute details form that pops upNew Table Attribute settings, set:
        Set the Attribute
        • Name: ReformattedPostCode
        • Leave the settings for the attribute as default (Type is String, Length is 50)
        Set the
        • Expression
        to
        • :

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

• • Press 

    Insert excerpt
    _finish _finish nopanel true

     on the New Table Attribute settings.

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 In the Reformatted Customer Details table settings, add a new attribute called CountyShortCode.
2. Leave the default settings.

3. Set the attribute expression 's Expression to:

   Code Block
   substring(in.County, 1, 3)

   
   

4. Press 

   Insert excerpt
   _finish _finish nopanel true

   .

Categorise bills as "LARGE" or "NORMAL"

Categorise To categorise bills as "LARGE" or "NORMAL":

1. Add In the Reformatted Customer Details table settings, add a new attribute called BillCategory
2. Leave the default settings.

3. Set the attribute expression 's Expression to:

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

   
   

4. Press 

   Insert excerpt
   _finish _finish nopanel true

   .

Set special processing flag _out

Finally, you will now set a special processing flag – this . 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 that is 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 In the Reformatted Customer Details table settings, add a new attribute called SpecialProcessing
2. Leave the default settings.

3. set Set the attribute expression 's Expression to:

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

   
   

4. Press 

   Insert excerpt
   _finish _finish nopanel true

   .

Check Point - Table Attributes

1. The Attributes section for the Reformatted Customer Details table should now look like this:
   1. Image Added

Run table

1. In the main table configuration formsettings for the table Reformatted Customer Details, press 

   Insert excerpt
   _finish _finish nopanel true

   .Remember to save your model layout,  Insert excerpt_save_savenopaneltrue.
2. Run analysis on the table Reformatted Customer Details.
3. Have a look at the data in the new this table – check that all your transformations have been correctly applied, like the image below:
4. Image Added
5. Remember to save your model layout, 

   Insert excerpt
   _save_saveModel _save_saveModel nopanel true

   .