3.44 mrjoin - Join Reference File According to Specified Range

Join fields from reference file according to specified range.

The value specified at r= parameter from the input data is matched with the range (value that falls above the first row and less than the next row) from the reference file, and subsequently joined with the value from the field specified at f= parameter. Use mnrjoin to join with complex conditions using range values. Consider using chgnum if there are not a lot of range.

Format

mrjoin r= [k=] [K=] [R=] [f=] [-n] [-lo] m=| [i=] [o=] [-nfn] [-nfno] [-x] [-q] [--help] [--version]

Parameters

`f=`	The field name(s) (multiple fields can be specified) to join from the reference file.
	When this is not defined, the all fields except the key specified at `K=` will be joined.
`m=`	Reference file name.
	Read from standard input if this parameter is not set (when `i=` is specified).
`r=`	Field name of the range for comparison [%n]
	Specify the field name from the input file.
	After the specified field is sorted (multiple fields can be specified), fields are joined
	The value is interpreted as numeric range when %n is specified, otherwise,
	it is treated as character range.
	The specified field should not contain NULL values or the data may not be processed properly.
`R=`	Field name containing range values in the reference file.
	When this parameter is not defined, the range is processed at `r=` parameter by default.
`k=`	Key field name(s) (multiple fields can be specified) from the input data for comparison
	Join records with same key fields in the input data `k=` and reference data `K=`.
`K=`	Key field name(s) (multiple fields can be specified) from the reference data for comparison
	This key field(s) from reference data is compared with the key field(s) from the input data specified at `k=` parameter,
	fields where records with common key are joined.
	This parameter is not required if the field name name is the same as the one defined at the `k=` parameter.
`-n`	Output NULL values when reference data does not consist of input data.
`-lo`	left-open interval
	The range with left open interval specified at `R=` parameter (greater than - below).

Examples

Example 1: Basic Example

Join category field low, middle,high to corresponding price range.

$ more dat1.csv
price
8
15
35
50
90
200
$ more ref1.csv
range,category
10,low
35,middle
80,high
100,
$ mrjoin r=price%n m=ref1.csv R=range f=category i=dat1.csv o=rsl1.csv
#END# kgrjoin R=range f=category i=dat1.csv m=ref1.csv o=rsl1.csv r=price%n
$ more rsl1.csv
price%0n,category
15,low
35,middle
50,middle
90,high

Example 2: Basic Example 2

$ mrjoin -lo r=price%n m=ref1.csv R=range f=category i=dat1.csv o=rsl2.csv
#END# kgrjoin -lo R=range f=category i=dat1.csv m=ref1.csv o=rsl2.csv r=price%n
$ more rsl2.csv
price%0n,category
15,low
35,low
50,middle
90,high

Example 3: Basic Example 3

$ mrjoin -n r=price%n m=ref1.csv R=range f=category i=dat1.csv o=rsl3.csv
#END# kgrjoin -n R=range f=category i=dat1.csv m=ref1.csv o=rsl3.csv r=price%n
$ more rsl3.csv
price%0n,category
8,
15,low
35,middle
50,middle
90,high
200,

Example 4: Join with different ranges for corresponding products

$ more dat2.csv
item,price
A,10
A,20
B,10
B,20
$ more ref2.csv
item,price,category
A,10,low
A,15,high
A,100,
B,10,low
B,35,high
B,100,
$ mrjoin k=item r=price%n m=ref2.csv f=category i=dat2.csv o=rsl4.csv
#END# kgrjoin f=category i=dat2.csv k=item m=ref2.csv o=rsl4.csv r=price%n
$ more rsl4.csv
item%0,price%1n,category
A,10,low
A,20,high
B,10,low
B,20,low

Related Commands

mchgnum : Specify a number range to replace / add value.

mjoin : Use this command to join matching strings instead of using numeric range.

mnrcommon : Use this command to select records rather than to join fields.