MUSASHI has a powerful calculation command - xtcal which performs various mathematical operations like spreadsheets programs such as Excel. Xtcal can carry out calcuation between different records, besides calculation between attributes of the same record (same row of transaction). The functions it will be explained in detail at "Advanced commands". In this lession, you will learn about "fundamental arithmetic operations", "date function" and "rounding up".
Summary of options and usage: xtcal
Example 1: Fundamental arithmetic operations
Goal: Create a master file of unit prices on all transactions . b
Methodology: Repulciate the script "xtcut.sh" and name the new script as "xtcal.sh". Select "Quantity" and "Amount" by xtcut, next, compute the unit price (amount/quantity) by xtcal and define a new attribute name with "-a". Now, use xtcut to extract the new field "unitPrice", followed by xtuniq command to remove duplicates. Then sort the list of unit price in ascending order as numerical values (use %n). The output will pipe to xtheader, define a new title and comment as need, update the -o parameter and write the result to the file "xtcal.xt".
Specify the parameters as follows:
Compute - -c '$Amount/$Quantity'
Note: Define the arithmetic expression with "-c". For arithmetic expression, the variable is preceded by "$"i and should be enclosed in single quotes. For more information on arithmetic operators, see Table 1 below.New Attributes - -a UnitPrice
Note: The calculated results will be stored as a new column, therefore, you will need to specify the name as an argument of -a.Your script will look like:
#/bin/bash
#===============================================================
# MUSASHI bash script
#===============================================================
#---- title
title="Tutorial"
#---- comment
comment="xtcal"
#---- variables
inPath="/home/public/tutorial"
#---------------------------------------------------------------
# command
#---------------------------------------------------------------
xtcut -f Quantity,Amount -i $inPath/dat.xt |
xtcal -c '$Amount/$Quantity' -a UnitPrice |
xtcut -f UnitPrice |
xtuniq -k UnitPrice |
xtsort -k UnitPrice%n |
xtheader -l "$title" -c "$comment" -o xtcal.xt #===============================================================When you are done, save and execute the script. The result should look as follows:
<?xml version="1.0" encoding="euc-jp"?>
<xmltbl version="1.00">
<header>
<title>
Tutorial
</title>
<comment>
xtcal
</comment>
<field no="1">
<name>UnitPrice</name>
</field>
</header>
<body><![CDATA[
59
63
64
65
66
69
70
72
74
75
78
79
81
arithmetic operators + addition - subtraction * multiplication / division % remainder comparison operators == numerical values on both sides are equal < the left numerical value is smaller than the right one <= the left numerical value is smaller than or equal to the right one > the left numerical value is greater than the right one >= the left numerical value is greater than or equal to the right one <> the left numerical value is not equal to the right one -eq character strings on both sides are equal -gt the left character string is smaller than the right one -ge the left character string is smaller than or equal to the right one -lt the left character string is greater than the right one -le the left character string is greater than or equal to the right one -ne the left character string is not equal to the right one =~ pattern matching by regular expression logical operators || disjunction of logical expressions on both sides && conjunction of logical expressions on both sides
Table 1.Arithmetic operators take two arguments on both sides and return the result. Comparison operators also take two arguments on both sides and returns "1" if the arguments meet the comparison condition and "0" otherwise. Logical operators also take two arguments on both sides and return 1 or 0 depending on the results of logical operations of both sides. Experiment how these operators work.
One Point: Specification of a constant< /caption>
Both operators and functions dealt with fixed values. In Table 1, "-eq" is a comparison operator for character operator for character strings, and "==" for numerical values. If an operand is an attribute, the type is automatically converted depending on the type of an operator. Yet, constants are defined differently for numeric values and character strings. If the constant is numeric, define as it is. If it is a character string, you need to put double quotes around them. The Date type has to be a character string. A logical value has to be specified as a numerical value which takes only 0 or 1.
Date Functions
Various functions related to date and time are available in xtcal. We will explain the two date functions which compute the number of days. Reuse xtuniq.sh which was already created, and rename as xtcalday.sh. In Lesson 8 , we have obtained a list of dates. Now, for each date in the list, let us compute the number of days from that date to "2001/06/01". Function "day" takes two arguments; "date1" and "date2" and computes the days in between. Thus, add this command after the command xtuniq:
xtcal -c 'day("20020601",$Date)' -a Days The script modified in this manner is shown as follows. Name the output file name as xtcalday.xt and modify the comment. After editing the script, save and execute it. You will verify that the number of days are correctly computed.
#/bin/bash
#===============================================================
# MUSASHI bash script
#===============================================================
#---- title
title="Tutorial"
#---- comment
comment="xtcal2"
#---- variables
inPath="/home/public/tutorial"
#---------------------------------------------------------------
# command
#---------------------------------------------------------------
xtcut -f Date -i $inPath/dat.xt |
xtuniq -k Date |
xtcal -c 'day("20020601",$Date)' -a Days |
xtheader -l "$title" -c "$comment" -o xtcalday.xt
#===============================================================Table 2 shows the date and time functions used in xtcal. Experiment each of these functions.
function meaning age(date1,birthday) compute the age at date=date1 datefmt(date,format) convert the format of a date dayadd(date1, number_of_days) return the date obtained by adding "date1" and "number_of_days" day(date2,date1) compute the number of days from date2 to date1 timefmt(time,format) convert the format of time time(time1,time2) compute the number of seconds from time2 to time1 now() return the current time today() return the current date
Numerical Functions
Various functions which deals with numerical values are available in xtcal. Based on exercise 2 in Lesson 9, calculate the AmountShare and QuantityShare for each 4-digit classification code in 2-digit classificiation code and rounds off the result to 2 decimal places.Methodology: Reuse the script xtshare.sh and rename as xtcal45.sh. In Lesson 9, we have created the script which computes the share of quantity and amount per 2 digit classification code previously, where share is a real number between 0 and 1. Share will be represented in % , and will be rounded off to two decimal places. This is done by multiplying the share by 100 and rounding off to two decimal places. The function "round" takes two arguments: the first argument specifies the number we want to round off and the second the base number. Round function obtains a multiple of the base number closest to the number given in the first argument. For example, when we round off 156.2841 with respect to 10. Then "round" returns 160. If the base number is 0.1, it returns 156.3.
Parameters:
Calulate - -c 'round($QunatityShare*100,0.01)'
New attribute - -a QuantityShare
Do the same to calculate amount share. Finally, specify the output file name as xtcl45.xt and modify the comment. The script for our example is shown as follows.
#/bin/bash
#===============================================================
# MUSASHI bash script
#===============================================================
#---- Title
title="Tutorial"
#---- Comment
comment="xtcal45"
#---- variables
inPath="/home/public/tutorial"
#---------------------------------------------------------------
# ¥³¥Þ¥ó¥É
#---------------------------------------------------------------
xtcut -f CategoryCode2,CategoryCode4,Quantity,Amount -i $inPath/dat.xt |
xtagg -k CategoryCode2,CategoryCode4 -f Quantity,Amount -c sum |
xtshare -k CategoryCode2 -f Quantity:QuantityShare,Amount:AmountShare |
xtcal -c 'round($QuantityShare*100,0.01)' -a QuantityShare% |
xtcal -c 'round($AmountShare*100,0.01)' -a AmountShare% |
xtheader -l "$title" -c "$comment" -o xtcl45.xt #===============================================================After editing the script, save and execute it. The result should become the following.
<?xml version="1.0" encoding="euc-jp"?>
<xmltbl version="1.00">
<header>
<title>
Tutorial
</title>
<comment>
xtcal45
</comment>
<field no="1">
<name>CategoryCode2</name>
<sort priority="1">
</sort>
</field>
<field no="2">
<name>CategoryCode4</name>
<sort priority="2">
</sort>
</field>
<field no="3">
<name>Quantity</name>
</field>
<field no="4">
<name>Amount</name>
</field>
<field no="5">
<name>QuantityShare</name>
</field>
<field no="6">
<name>AmountShare</name>
</field>
<field no="7">
<name>QuantityShare%</name>
</field>
<field no="8">
<name>AmountShare%</name>
</field>
</header>
<body><![CDATA[
11 1101 6247 2388930 0.182431 0.179297 18.24 17.93
11 1102 1515 546193 0.044243 0.040994 4.42 4.1
11 1103 365 145373 0.010659 0.010911 1.07 1.09
11 1104 2173 849791 0.063458 0.06378 6.35 6.38
11 1105 2249 860320 0.065678 0.06457 6.57 6.46
Table 3 shows other numerical values functions which can be used in xtcal.
function meaning sum(value1, value2, ...) compute the sum of value1, value2, ... sqrt(value) compute the square root power(value,exponet) compute the power of a numerical value abs(value) compute the absolute value of a numerical value round(value, base number) round off a number to a multiple of a base number up(value, base number) round up a number to a multiple of a base number down(value, base number) round down a number to a multiple of a base number exp(value) compute the power of e ln(value) compute the logarithm with base equal to e log(value, base) compute the logarithm with specified base log2(value) compute the logarithm with base equal to 2 log10(value) compute the logarithm with base equal to 10 pi() compute π
Table 3.
Other Functions
There are surprisingly many other functions available in xtcal listed in Table 4 below. We will explain how to use them in the subsequent lessons. In particular, inter-record functions allow us to carry out various types of useful calculations.
Function Description Character string function cat(string1, string2, ..., token) concatenate character strings with token regexStart(string, regular expression) return the start position of the string which matches a regular
expressionregexEnd(string, regular expression) return the last position of the string which matches a regular
expressionregexLen(string, regular expression) return the maximum length of a portion of the string which
matches a regular expressionLogical function if(condition, string1, string2) return string 1 if the condition holds and string 0 otherwise not(logical value) negation of a logical value inter-record function line() line number currently processed fldCnt() number of attributes keyLine() line number within the current key keyCnt() number of keys currently processed keyNo() sequential number of the key currently processed prvFldA(line number, attribute) return the value of attribute A of the line whose absolute
line number within the specified key is NprvFldR(line number, attribute) return the value of attribute A of the line whose relative line
number with respect to the current line within the specified key is N.prvRslA(line number, attribute) return the calculation result for the line whose absolute line
number within the same key as the current one is N.prvRslR(line number, attribute) return the calculation result for the line whose relative
line number within the same key as the current one is N.Exercises
Let's apply xtcal on the following reports. Check your results with the scripts and output files given below.
Report Description
Script name Output file (xt) Output file (html) Average quantity and amount per day and drop the decimal places. xtcal1.sh xtcal1.xt xtcal1.html Total amount per day with sales tax (5%) included
(fractional portion is dropped)xtcal2.sh xtcal2.xt xtcal2.html List of dates 45 days after each unique date in the dataset xtcal3.sh xtcal3.xt xtcal3.html Number of days from today to each date in the dataset
xtcal4.sh xtcal4.xt xtcal4.html