This command calculate the cumulative value for each key field defined by the user.
Summary of options and usage: xtaccum
Editing your script
Startup FD. Repulciate the script "xtcut.sh" used in the previous lesson by selecting xtcut.sh and press "c+CTRL", you will then be prompted for a new name, name the new script as "xtaccum.sh"Goal: Compute the cumulative value of quantity and amount by day from the beginning of the year to the end of the year.
Methodology: Select "date", "quantity" and "amount" by xtcut, then apply xtagg to compute the total quantity and amount based on each unique "date". Then pipe the result to xtaccum to calculate the accumulative value for each day. Send the output to xtheader, define a new title and comment as need, update the -o parameter and write the result to the file "xtaccum.xt".
Specify the parameters as follows:
Key - -k Date
Note: "-k" is the key attribute on which the unit of accumulation is based on. In this example, cumulative value is based on the unique value of all dates in the dataset, therefore, this parameter will be omitted.Attributes - -f Quantity:AccumQuantity,Amount:AccumAmount
Note: The argument -f is the field on which the cumulative value will be calculated. The calculated values will be appended to the last column in the dataset, therefore, we need to define a new attribute by specifying "xtaccum -f Quantity:AccumQuantity,Amount:AccumAmount".Your script will look like:
#/bin/bash
#===============================================================
# MUSASHI bash script
#===============================================================
#---- title
title="Tutorial"
#---- comment
comment="xtaccum"
#---- variables
inPath="/home/public/tutorial"
#---------------------------------------------------------------
# command
#---------------------------------------------------------------
xtcut -f Date,Quantity,Amount -i $inPath/dat.xt |
xtagg -k Date -f Quantity,Amount -c sum |
xtaccum -f Quantity:AccumQuantity,Amount:AccumAmount |
xtheader -l "$title" -c "$comment" -o xtaccum.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>
xtagg
</comment>
<field no="1">
<name>Date</name>
</field>
<field no="2">
<name>Quantity</name>
</field>
<field no="3">
<name>Amount</name>
</field>
<field no="4">
<name>AccumQuantity</name>
</field>
<field no="5">
<name>AccumAmount</name>
</field>
</header>
<body><![CDATA[
20020101 161 60034 161 60034
20020102 40 13959 201 73993
20020103 155 62402 356 136395
20020104 107 41467 463 177862
20020105 52 21283 515 199145
20020106 106 43070 621 242215
20020107 87 31458 708 273673
20020108 98 40726 806 314399
20020109 152 61779 958 376178
20020110 144 51501 1102 427679
20020111 150 62140 1252 489819
20020112 177 69727 1429 559546
Example 2: Cumulative value of total amount of total quantity for each day in descending order
Methodology: Before commencing the xtaccum command, sort the Date attribute by descending order "xtsort -k Date%r". The corresponding scripts will look as follows:
xtcut -f Date,Quantity,Amount -i $inPath/dat.xt | xtagg -k Date -f Quantity,Amount -c sum | xtsort -k Date%r | xtaccum -f Quantity:AccumQuantity,Amount:AccumAmount | xtheader -l "$title" -c "$comment" -o xtaccum.xt
20021231 139 55483 139 55483
20021230 65 25010 204 80493
20021229 121 46492 325 126985
20021228 146 50974 471 177959
20021227 148 59424 619 237383
20021226 88 34793 707 272176
20021225 132 45721 839 317897
20021224 132 53431 971 371328
20021223 178 69113 1149 440441
Example 3: For each 2-digit classification code, calculate the cumulative value of quantity and amount for each 4-digit classification code
Before running the xtaccum command, sort the data by 4 digit classification codes in each 2 digit classification code. First, execute "xtsort -k CategoryCode2,CategoryCode4%r". Here the 2-digit classification code is the base unit for cumulation. Every time a 2-digit classification code changes, the cumulative value will reset to zero. Here "CategoryCode2%r" specifies the order of cumulation (descending order of 4-digit classification codes). The script should be modified as shown below.
xtcut -f CategoryCode2,CategoryCode4,Quantity,Amount -i $inPath/dat.xt |Save and execute the script, the result should look as follows:
xtagg -k CategoryCode2,CategoryCode4 -f Quantity,Amount -c sum | xtsort -k CategoryCode2,CategoryCode4%r |
xtaccum -k CategoryCode4 -f Quantity:AccumQuantity,Amount:AccumAmount |
xtheader -l "$title" -c "$comment" -o xtaccum.xt
11 1197 746 324173 746 324173
11 1121 1872 737854 2618 1062027
11 1120 523 248100 3141 1310127
11 1119 989 336226 4130 1646353
11 1118 1288 484484 5418 2130837
11 1117 1279 471076 6697 2601913
11 1116 1804 647799 8501 3249712
11 1115 1092 414138 9593 3663850
11 1114 372 154587 9965 3818437
| However, there are some commands where sorting must preceed such as xtaccum, xtbest, xtslide and xtpattern where data have to be sorted in an appropriate order. |
Let's apply xtagg 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) |
| Cumulative values of quantity and amount for manufacturers (sort manufacturer code by ascending order) | xtaccum1.sh | xtaccum1.xt | xtaccum1.html |
| Cumulative values of quantity and amount in 2-digit classification code classified by manufacturer in descending order | xtaccum2.sh | xtaccum2.xt | xtaccum2.html |