Command Overview

Note: this feature is only available in version 1.3 and above

The calculate command is used to compute a value based upon data in the input source. The command supports three sources of data for testing:

1. Data file in tab separated value format
2. WorkSheet
3. Selected range within a worksheet

The calculate command can be used to produce interim results, e.g. calculated values, based upon a variety of criteria such as:

• numeric values, e.g. less than $200, between $50 - $100, etc
• text values, e.g. division name of "AB", last name starts with B, etc
• date values, e.g. between 1-1-2005 and 6-30-2006, weekday of Saturday, etc.
• computed values, e.g. tax > invoice amount * .05, selling price < cost, etc.
• complex criteria, e.g. based upon a combination of one or more of the above

The key to the calculate command is the "amount" statement, which defines the criteria used to make the computations. An amount statement is specified as amount="<computation statement or statements>".

An amount statement is one which evaluates to various types of data, depending upon the formula used true or false, depending upon the input data. All variables are specified with a leading "$" to indicate that they are a variable (and not a text literal). The variable names correspond with the top row of the data in a worksheet or with the first row of data contained in a file in tab separated value format.

Examples of amounts include:

• $InvoiceAmount * .04
• left($SupplierNo,2)
• ($Cost - $AccumDepr)

Calculations can also be made to test if a composition is true or false (the result will be shown as the text "TRUE" or "FALSE")

Examples include:

• $InvoiceAmount < 100
• left($SupplierNumber,2) = "27"
• ($Cost - $AccumDepr) > $Salvage

Syntax

The command is entered on the command line as the command name ("calculate", or just "ca") followed by one or more command parameters. Each command parameter consists of an argument name, immediately followed by an equal sign ("=") and then immediately followed by a value. The value may be enclosed with quotation marks, and must be enclosed by quotation marks if the value consists of any embedded spaces.

An example of a command parameter to specify that the file name to be processed is test.txt is entered as "file=test.txt" (without the quotations). Each argument on the command line must consist of only valid parameter names. If a required parameter name is missing, then the command line will be colored yellow, an error message will be displayed on the Excel status bar and an audible tone will be sounded.

The following is a listing of all parameter values for the calculate command:

An example

The command to be perform a calculation using the file named \test\invoice\tran.txt , storing the results on the worksheet named "Extract Data " is as follows:

ca ds=file file=\test\invoice\tran.txt recap="Extract Data" amount="$Cost - $Salvage" col=$Book

The effect of this command is to load the file into an excel worksheet and replace the value (if any) in the existing column named "Book" with the computed amount. Note that this same result could have been achieved by first doing an extract with a condition which will always be true , e.g.. "1=1" and then running a calculate command on the data in the sheet.

Main Form