Basic Commands
Lesson 4: Sorting (xtsort)

The xtsort command sorts the records on the defined key attribute in the order specified.

Summary of options and usage: xtsort

Using xtsort

Goal: Sort the total amount and quantity in reverse order by date. Extract Date, Quantity and Amount from the sample data set, calculate the total amount and quantity, and sort the result in reverse order by date.

Step1: Editing your script

In FD console, make a copy from xtagg.sh and rename it to xtsort.sh b y pressing "c+CTRL".

copy the file xtagg.sh as a new name on the current directory
new name : xtsort.sh
cp /home/public/lesson/basic/xtagg.sh /home/bear/lesson/basic/xtsort.sh

Methodology: Extract Date, Quantity and Amount from the sample data set with xtcut command, then redirect the output to xtagg with "|" whic returns the total amount of quantity and amount for each unique date. Pipe the output to xtsort , define the key attributes and parameters to be sorted. Finally, pass the output to xtheader set a title and comment of the resulting data set.

Step 2: Defining Attributes and Options

Specify the parameters as follows:

Key - -k Date
Note: DATE is the key attribute where you want the data to be sorted by. An extra argument "%r" or "%n" can be added after the attribute name to define the order in which the key attribute will be arranged in. When the extra argument is not defined, data will be sorted in descending order by character. You may also define more than one key attribute to be sorted.

The script will look as follows:

#/bin/bash
#===============================================================
# MUSASHI bash script
#===============================================================

#---- Title
title="Tutorial"

#---- Comment
comment="xtsort"

#---- variables
inPath="/home/bear/tutorial"

#---------------------------------------------------------------
# command
#---------------------------------------------------------------
xtcut -f Date,Quantity,Amount -i $inPath/dat.xt |
xtagg -k Date -f Quantity:TotalQuantity,Amount:TotalAmount -c sum |
xtsort -k Date%r |
xtheader -l "$title" -c "$comment" -o xtsort.xt
#===============================================================

Step 3. Running the script

After editing, save and execute the script. Your result should look as follows, notice that the "Date" is now sorted in descending order:

<?xml version="1.0" encoding="euc-jp"?>
<xmltbl version="1.00">
<header>
<title>
Tutorial
</title>
<comment>
xtsort
</comment>
<field no="1">
<name>Date</name>
<sort priority="1">
</sort>
</field>
<field no="2">
<name>TotalQuantity</name>
<sort priority="2">
</sort>
</field>
<field no="3">
<name>TotalAmount</name>
<sort priority="3">
</sort>
</field>
</header>
<body><![CDATA[
20021231 139 55483
20021230 65 25010
20021229 121 46492
20021228 146 50974
20021227 148 59424
20021226 88 34793
20021225 132 45721
20021224 132 53431
20021223 178 69113
20021222 175 66270
20021221 115 41608
20021220 90 34318
20021219 105 40912
20021218 133 48708
20021217 162 62962
20021216 117 42259
20021215 138 56530
20021214 124 48702
20021213 130 49896
¡§
:

Additional Examples

Additional ways of sorting data with xtsort are listed as follows:

Case 1: To sort the records by amount in the ascending order, you need to specify the "%n" argument for amount "-k amount%n" as amount is a numerical value.

Case 2: To sort the records by amount in the ascending order, specify "-k amount%n%r" (the order of %r and %n does not matter).

Case 3: To sort the records in descending order by "date" and ascending order by "amount", you may specify the argument "-k date%r, amount%n. The result will be arranged in the descending order by date, among records with the same date value, the amount with a larger value will come first.

Exercises

Let's practice xtcount on the reports below. Check your results with the scripts and output files given below.

Report Description Script name Output file (xt) Output file (html)
Extract a dataset to show the total quantity and amount per day, and sort the total amount in ascending order xtsort1.sh xtsort1.xt xtsort1.html
Extract a dataset to show the total quantity and amount of items sold per day, and arranged the amount in descending order xtsort2.sh xtsort2.xt xtsort2.html
Find out the total quantity and total amount sold for each day for each 4 -digit catagory code. Then sort the data by date and total quantity in ascendin g order. xtsort3.sh xtsort3.xt xtsort3.html
Find out the total quantity and total amount sold for each day for each 4 -digit catagory code. Then sort the data by date in descending order and total quantity in descending numerical order. xtsort4.sh xtsort4.xt xtsort4.html

Home  |  Next> Lesson 5: Extract substrings