Tutorial Creating a Simple Rule Set & Querying It

Top     Installation     Introduction     Samples     Tutorial     Reference     Release Notes

Rules

Rules set values for facts when their conditions are met. The basic rule statement is:

Fact = Value WHEN Conditions

A fact name is made up of letters, numbers and the underscore ( _ ). It must start with a letter and cannot have embedded blanks.

A value is a number, text, or date-time. Text values need to be enclosed in single or double quotes. Date-times are discussed later.

Conditions is a boolean expression indicating when the fact takes the value specified in the rule. Keywords AND and OR can be used to create complex conditions.

For the first simple example we will consider two rules for setting the price of some unit.

• The unit price is 10 when the quantity ordered is < 20
• The unit price is 8 when the quantity ordered is >= 20

In ARulesXL those rules can be expressed:

UnitPrice = 10 When Quantity < 20
UnitPrice = 8 When Quantity >= 20

Rule Sets

A rule set is a named collection of rules. For the first example, the name PriceRules will be given to the two rules above. Rule sets can have any number of arbitrarily complex rules.

Rule set names must be valid range names in Excel. This means they cannot have embedded blanks, and cannot be cell names. So A1 or BB24 are NOT valid rule set names, nor is Price Rules.

Rules in rule sets are declarative, meaning the author of the rules does not have to worry about the execution order of the rules. This is why rules are so much easier to develop and read than procedural IF statements.

A reasoning engine makes the declarative rules possible. It dynamically determines which rules apply based on varying input values.

To make use of a rule set, the reasoning engine is queried, much like a database engine is queried to get answers from a database. The function RQuery() is used in ARulesXL to pose queries. The simplest form is:

=RQuery(RuleSetName, Query)

RuleSetName is the name of the rule set to be queried.

Query is an Excel text string of the form:

FIND Fact WHEN InputFact1 = Value1 AND InputFact2 = Value2 ...

To find a unit price using the PriceRules rule set, this query would be entered in a cell external to the rule set:

=RQuery( PriceRules, "FIND UnitPrice WHEN Quantity = 25")

Implementing the Example

1. Widen the A column and select cells A1 to A4 in the spreadsheet.
2. From the main menu select ARulesXL | New Ruleset...
3. When prompted, enter the name PriceRules, and check the range for the new rule set
4. The name will appear in A1 and a border will appear around the cells indicating the end of the range.
5. Type these two rules in A2 and A3:

   UnitPrice = 10 when Quantity < 20
   UnitPrice = 8 when Quantity >= 20

6. From the ARulesXL menu or the right mouse menu select Load Modified Rules. This loads the rules into the ARulesXL reasoning engine.

Next, call the rule query function, RQuery(), in cell A6.

=RQuery( PriceRules, "Find UnitPrice when Quantity = 25")

The result, 8, should appear in the cell. The spreadsheet should look like:

This simple example illustrates a number of points about ARulesXL:

• Rules are defined in Excel ranges which become ARulesXL rule sets.
• The first cell in the range contains the name of the rule set.
• A rule is constructed with an action on the left, the key word WHEN, and conditions on the right.
• The rules set and test facts.
• The rule sets are queried with the function RQuery(). The first argument is the rule set name. The second argument is a request to find the value of a fact when other fact values are optionally input.

Note - The rules and queries are NOT case sensitive, so UnitPrice and unitprice are equivalent.

Order of Rules

Rules for different facts can be put in any order in a rule set. The reasoning engine uses indexes to find them when needed.

Rules for the same fact, UnitPrice in our example, can also be put in any order as long as they are not ambiguous. If there are multiple rules which are both possible, then the first rule which fits will be used.

The two UnitPrice rules in the example are both coded with conditions that exclude each other, so it doesn't matter which order they appear in. Knowing the order, however, lets you omit the conditions on the second rule, knowing it will only be tried if the first rule fails:

UnitPrice = 10 WHEN Quantity < DiscountQuantity
UnitPrice = 8

While this is probably poor style for the example, it can be a useful technique for setting a default value for a fact by having the last rule for the fact simply set the value.

Syntax Checking

ARulesXL automatically checks the syntax of your rules. To try this in PriceRules make a syntactic error by deleting the = after UnitPrice in cell A3.

The syntax check will set a comment on each cell indicating the nature of the error. To get the error message, click on the small triangle in the upper left corner of the cell. This is what it looks like for this error:

Resizing, Renaming and Removing Rule Sets

If you want to change the size of a rule set, simply insert more rows and/or columns into the range with the Excel Insert command.

If you want to delete a rule set, highlight all the cells in the rule set and select ARulesXL | Remove Rule Set. This turns the contents of the cells in the rule set range into plain text. You can convert them back to a rule set by selecting the cells and using the ARulesXL | New Rule Set... command.

To rename a ruleset, just edit the rule set name at the top of the rule set. You will need to change any RQuery() functions that used the old ruleset name.

Copyright ©2005-7 Amzi! inc. All Rights Reserved. ARulesXL and Amzi! are trademarks or registered trademarks of Amzi! Microsoft and Excel are trademarks or registered trademarks of Microsoft Corporation.