3.26 mjoin - Join field(s) from Reference File

Compare the key field(s) from the input file specified at the k= parameter with the ones from the reference file, field from the reference file specified at f= parameter are joined for records with common key values in both files. The key fields from the reference file must be unique. Use the mnjoin command when there are more than one record with the same key values in the reference file. If f= is not set, all columns are joined except the key field in reference file.

Format

mjoin k= [f=] [K=] [-n] [-N] m=| i= [o=] [-nfn] [-nfno] [-x] [-q] [--help] [--version]

Parameters

`k=`	Specify the key field name from input data field and at the `K=` parameter.
	Join records with the same field(s) from reference data.
	Set to NULL value when key fields do not match with the fields from reference file specified at `K=`.
`f=`	Field name of the reference file to join.
	When this parameter is not set, all fields except the key field will be joined.
`m=`	Specify list of reference file(s).
	Read from standard input when this parameter is not set (used when input data is specified at `i=`).
`K=`	List of field names from reference data for matching.
	Specify key field(s) from reference data to join with records with the key field from
	the input data defined at the `k=` parameter.
	Set to NULL value when key fields do not match with the fields from input file specified at `k=`.
	The key field(s) name from the reference file does not need to be the same as the `k=` parameter.
`-n`	Output NULL values when reference data does not consist of input data.
`-N`	Output NULL values when input data does not consist of reference data.

Examples

Example 1: Basic Example

Join the field cost from the reference file for records where the values of the item column from the input file is the same as the values in item column in the reference file.

$ more dat1.csv
item,date,price
A,20081201,100
A,20081213,98
B,20081002,400
B,20081209,450
C,20081201,100
$ more ref1.csv
item,cost
A,50
B,300
E,200
$ mjoin k=item f=cost m=ref1.csv i=dat1.csv o=rsl1.csv
#END# kgjoin f=cost i=dat1.csv k=item m=ref1.csv o=rsl1.csv
$ more rsl1.csv
item%0,date,price,cost
A,20081201,100,50
A,20081213,98,50
B,20081002,400,300
B,20081209,450,300

Example 2: Output unmatched data

Join the cost field for records with common key values in the item field from the input file and reference file, join cost item. At the same time, join all keys from the reference file if the value in the reference file is not in input data range, and set as NULL values.

$ mjoin k=item f=cost m=ref1.csv -n -N i=dat1.csv o=rsl2.csv
#END# kgjoin -N -n f=cost i=dat1.csv k=item m=ref1.csv o=rsl2.csv
$ more rsl2.csv
item%0,date,price,cost
A,20081201,100,50
A,20081213,98,50
B,20081002,400,300
B,20081209,450,300
C,20081201,100,
E,,,200

Related Command

mnjoin : Use mnjoin to duplicate key from reference file. mpaste : Join according to row number.

mcommon : Use mcommon to select records instead of joining.