4.1 mcal - Computation Between Columns

Define the computation formula at the c= parameter, and name the new data attribute at the a= parameter. The output of mcal is limited to 1 result without exception to simplify the program. For details of the calculation formula, please refer to the section on “Components in the expression".

Format

mcal a= c= [i=] [o=] [-nfn] [-nfno] [-x] [precision=] [--help] [--version]

Parameters

a=

Specify the new column name to store the calculated field.

c=

Define an expression with a combination of calculation functions available.

Examples

Basic usage of mcal is illustrated in the following example. For more information on the explanation and usage of individual functions and operators, please refer to the corresponding reference.

# Input data (dat1.csv)
customer,quantity,unitprice
A,3,10
B,1,15
C,2,20

$ mcal c='${quantity}*${unitprice}' a=amount i=dat1.csv
customer,unit price,amount
A,3,10,30
B,1,15,15
C,2,20,40

$ mcal c='${quantity}*${unitprice}<=30' a=amountbelow30 i=dat1.csv
customer,unit price,amountbelow30
A,3,10,1
B,1,15,1
C,2,20,0

$ mcal c='if(top(),${unit price},#{}+${unitprice})' a=AccumUnitprice i=dat1.csv
customer,unitprice,AccumUnitprice
A,3,10,10
B,1,15,25
C,2,20,45

Considerations when using shell

When using BASH shell on UNIX operating systems, the symbol of operators often have special meaning to the shell. For example, a shell variable is represented by the $ symbol followed by a character string. On the other hand, mcmd uses the $ symbol to refers to the value of the data field. Thus, mcmd variables is enclosed in squiggly brackets preceded by the $ symbol in order not to be misinterpreted as shell variables.

$ mcal c='${date}-10'

Error message

#ERROR# unknown function or operator

This error message appears when there is an error in the specified operator or function. For instance, refer to the error message from the concatenation of strings function cat.

 $ mcal c='cat("-",1,2)'
 ERROR : unknown function or operator: cat_SNN(cat_SN) (kgcal)

The string before underscore character in "cat_SNN" indicates the function name cat, subsequently, SNN refers to the type of the argument. S refers to string type, N refers to number type, D refers to date type, and T is the time type, B refers to boolean type. The 3 characters (SNN) specifies 3 arguments. Thus, this error message means “arguments SNN of cat function” is not registered. The second and third argument is converted to string as follows.

 $ mcal c='cat("-","1","2")'

The above returns an error message with 2 characters in parenthesis (SN), this refers to specification of variable or number at the first two parameters, however, only 1 variable is expressed correctly in the function.

% $ mcal c='cat(${商品ID},${単価},"-")' a=商品ID-単価 i=dat1.csv o=rsl1.csv

Related Commands

msel : Use this command to select the row from the computation result.