3.14 mcross - Crosstab

Build a crosstab. Transpose the fields specified at s= parameter horizontally, itemize the array of data specified at the f= parameter to the corresponding key specified at k=.

Format

mcross f= s= [a=] [k=] [v=] [i=] [o=] [-nfn] [-nfno] [-x] [-q] [--help] [--version]

Parameters

`f=`	Field name specified will become cross tab fact cell.
	When multiple fields are specified, the variables will be transposed to multiple rows.
	The `fld` field is created to identify multiple rows of the field.
	Specify the name of field at f= as the column variable.
	Use `a=` parameter to rename the field `fld`.
`s=`	Field names of transposed field.
	The data series in the field becomes column name of transposed rows.
`a=`	Specify the field name to transpose the row of field names.
	The field name is set to `fld` by default when this parameter is not defined.
`k=`	Key field name [aggregate key break processing]
	Key to transpose data into horizontal rows.
`v=`	Null value replacement string
	Replace null character with a character defined at `v=` parameter.

Example

Example 1: Basic Example

Expand the array of date horizontally and itemize quantity to the corresponding item.

$ more dat1.csv
item,date,quantity,price
A,20081201,1,10
A,20081202,2,20
A,20081203,3,30
B,20081201,4,40
B,20081203,5,50
$ mcross k=item f=quantity s=date i=dat1.csv o=rsl1.csv
#END# kgcross f=quantity i=dat1.csv k=item o=rsl1.csv s=date
$ more rsl1.csv
item%0,fld,20081201,20081202,20081203
A,quantity,1,2,3
B,quantity,4,,5

Example 2: Restore the original input data

Restore the output from Example 1 to the original input data with mcross.

$ more rsl1.csv
item%0,fld,20081201,20081202,20081203
A,quantity,1,2,3
B,quantity,4,,5
$ mcross k=item f=2008* s=fld a=date i=rsl1.csv o=rsl2.csv
#END# kgcross a=date f=2008* i=rsl1.csv k=item o=rsl2.csv s=fld
$ more rsl2.csv
item%0,date,quantity
A,20081201,1
A,20081202,2
A,20081203,3
B,20081201,4
B,20081202,
B,20081203,5

Example 3: Crosstab with multiple fields

Display crosstab results on two fields quantity,price.

$ mcross k=item f=quantity,price s=date i=dat1.csv o=rsl3.csv
#END# kgcross f=quantity,price i=dat1.csv k=item o=rsl3.csv s=date
$ more rsl3.csv
item%0,fld,20081201,20081202,20081203
A,quantity,1,2,3
A,price,10,20,30
B,quantity,4,,5
B,price,40,,50

Example 4: Reverse data sequence

Restore the sequence of the items that was expanded horizontally.

$ mcross k=item f=quantity,price s=date%r i=dat1.csv o=rsl4.csv
#END# kgcross f=quantity,price i=dat1.csv k=item o=rsl4.csv s=date%r
$ more rsl4.csv
item%0,fld,20081203,20081202,20081201
A,quantity,3,2,1
A,price,30,20,10
B,quantity,5,,4
B,price,50,,40

Related Command

mtra : Creates the same data image of horizontal transposition, but mtra output the vector as a single field in the output.