Reference

Top Installation Introduction Samples Tutorial Reference Release Notes

Spreadsheet Functions

These are the spreadsheet functions that are available in the ARulesXL add-in:

RQuery() — Queries a rule set. (Used outside a rule set)

RCell() — Loads a cell value into an property. (Used in a rule set)

RArray() — Loads multiple cell values into an array or list property. (Used in a rule set)

RXLDependency() — Used with multiple rule sets to ensure they are synchronized with Excel. (Used in a rule set)

RCell( FactName, Cell )

RCell() is used in a rule set to load a single value into a single fact. The FactName is the fact name of the new value. The Cell is the location of the value.

For example:

=RCell( "part_discount", D8 )
=RCell( "free_gift:", true )
=RCell( "income['Q1']", C4 )

RQuery( RuleSet, QueryText, Cell_1, ..., Cell_N )

RQuery() is used in any spreadsheet cell and returns a fact value obtained by querying a rule set.

RuleSet can be:

a rule set name, or
the address of another cell with an RQuery() in it.

If RuleSet is a rule set name, then, because that name is also the Excel range name for the rule set, the RQuery() will be recalculated whenever there are changes to the rule set. The query is re-executed using RuleSet as the default rule set for the FIND in the QueryText.

If RuleSet is the address of another cell with RQuery(), then the query is recalculated whenever the other query is recalculated. In this case, the query is executed without re-deriving intermediate results. In other words, the query builds on whatever work was performed by the first query. This is useful when you have multiple queries looking for additional information from a prior query. Multiple RQuery()s can be chained together using this technique.

QueryText is a string with a query and embedded markers of the form Cell_N which are replaced with the optional cell arguments. _1 is replaced with the first cell argument, _2 with the second, etc.

The syntax of a query is:

"FIND Fact [IN RuleSet] [WHEN Fact1 = Value1 AND Fact2 = Value2 AND ...]"

The IN clause is optional and is used to specify a default rule set for the query that is different from the one derived from the first argument of the RQuery() function call.

The WHEN clause is optional and is used to set input values for other facts. Any number of ANDs can be used to set additional fact values for the query.

Example, using a rule set to find a fact with no input facts (presumably these are set directly in the rule set using RCell() or RArray()):

=RQuery(LoanRules, "find loan_status")

Example using input values in the query and multiple rule sets specified in a RXLDependency() function call:

=RQuery(AllRules, "FIND price WHEN part = widget and quantity = _1", C10)

AllRules
=RXLDependency(PriceRules, WidgetRules, PartRules)

Example with explicit IN clause overriding the default:

=RQuery(AllRules, "FIND price in PriceRules WHEN part = widget and quantity = _1", C10)

AllRules
=RXLDependency(WidgetRules, PartRules, PriceRules)

If there is a syntax error in the query, or a runtime error executing the query, then RQuery() returns the text of the error message instead of a value for the fact.

RArray( FactName, Range, [hasRowHeaders], [hasColumnHeaders], [isVector])

RArray() is used in a rule set to load data from a 2-dimensional table or a single vector into a rule set. The data can optionally have row and/or column headers. If no headers are provided, the data is indexed numerically. The FactName is the name of the property that will hold the values. It will be referenced with array indices. The hasRowHeaders and hasColumnHeaders parameters are optional and specify whether or not the table has row and/or column headers. The last parameter, isVector, which is also optional, specifies whether or not to use a column index when loading a single column of data.

For example, given the table:

Q1 Q2 Q3 Q4

Revenues $100.00 $120.00 $140.00 $190.00

Expenses $90.00 $115.00 $100.00 $160.00

Income $10.00 $5.00 $40.00 $30.00

Taxes $2.50 $1.25 $10.00 $7.50

This query loads the table using its row and column headers:

=RArray("data", A1:E5, TRUE, TRUE)

and will load properties and values like these:

data['Revenues', 'Q1'] = 100
data['Income', 'Q4'] = 160
data['Taxes', 'Q2'] = 1.25

Note, you can load multiple tables into the same fact. For example:

=RArray("data[2004]", A1:E5, TRUE, TRUE)
=RArray("data[2005]", G1:K5, TRUE, TRUE)
...

essentially creates a 3-dimensional array with values like these:

data[2004, 'Revenues', 'Q1'] = 100
data[2005, 'Income', 'Q4'] = 220

An asterisk (*) can be used for one dimension when referring to a table. The value is an array/list of that row or column, no matter what dimension it is. Using the previous .data examples, and assuming the 2005 Q2 Expense is 130:

data[2004, 'Expenses', *] = [90, 115, 100, 160]
data[*, 'Expenses', 'Q2'] = [115, 130]

There are other ways to express multi-dimensional arrays:

=RArray("pl_west_2004", A1:E5, TRUE, TRUE)	pl_west_2004['Income', 'Q1']
=RArray("rpl['West', 2004] ", A1:E5, TRUE, TRUE)	rpl['West', 2004, 'Income', 'Q1']

To load the data without headers use:

=RArray("data", B2:E5, FALSE, FALSE)

which results in:

data[1, 1] = 100
data[3, 4] = 160
data[4, 2] = 1.25

To load just a row of a table use:

=RArray("data", B2:E2, FALSE, FALSE, TRUE)

which loads these properties and values:

data[1] = 100
data[2] = 120
data[3] = 140
data[4] = 190

To load this table of user inputs:

Price 10000

Loan Amount 8000

Credit Rating 120

Use

=RArray("input", A1:B3, TRUE, FALSE, TRUE)

This loads the following properties and values:

input['Price'] = 10000
input['Loan Amount'] = 8000
input['Credit Rating'] = 120

Note, if the last parameter was FALSE, then the data would have been loaded as:

input['Price', 1] = 10000
input['Loan Amount', 1] = 8000
input['Credit Rating', 1] = 120

RXLDependency( RuleSet_1, ..., RuleSet_N )

RXLDependency() is used in a rule set to specify what other rule sets it depends upon for applications with multiple rule sets. Used as a function called in a rule set, it ensures the rule sets are automatically synchronized with the rule engine whenever changes occur in the rule set.

Note — RXLDependency() establishes spreadsheet dependencies between rule sets and/or queries, allowing Excel recalculation to keep queries and rule sets synchronized. RXLDependency() does NOT establish any logical relationships between rule sets. Logical relationships are specified with explicit references to other rule sets and/or the used of INHERIT FROM statements.

The use of RXLDependency() is required when a query involves multiple rule sets, either because:

The first rule set explicitly calls other rule sets; or
The first rule set is a subset of other rule sets.

Note that the rule set will be considered to have changed whenever an input value to the rule set changes, as well as when changes are made directly to the rule set.

Example:

=RXLDependency(PriceRules, WidgetRules, PartRules)

Open in New Window to Print

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.

	Q1	Q2	Q3	Q4
Revenues	$100.00	$120.00	$140.00	$190.00
Expenses	$90.00	$115.00	$100.00	$160.00
Income	$10.00	$5.00	$40.00	$30.00
Taxes	$2.50	$1.25	$10.00	$7.50