3.12 mcommon - Select Common Records in Reference File

Compare records in the input file with the ones in the reference file, at which reference file is specified in m= parameter. Set the key parameter at k= for selecting records common in both files.

Format

mcommon k= [K=] [u=] [-r] m=| i= [o=] [-nfn] [-nfno] [-x] [-q] [--help] [--version]

Parameters

`k=`	List of key field(s) to match the input data (Multiple keys can be specified)
	Records in the input file matching the key field(s) in the reference file specified at `K=` is selected.
	Records with same key values will appear in consecutive rows.
`m=`	Specify reference file name
	Standard input is used when this parameter is omitted (when `i=` option is specified).
`K=`	Key field(s) to match with from the reference data (Multiple keys can be specified).
	Records in the reference file that matches the key field specified in the `k=` parameter is selected.
	The parameter is not required if the key field is the same on both input data and reference file.
	Records with same key values will therefore appear in consecutive rows.
`u=`	File name of output with unmatched records.
`-r`	Reverse selection
	Compare the value specified at `k=` parameter from the input file
	with the value from the reference file specified at `m=` parameter,
	and select unmatched record(s) from the input file.

Examples

Example 1: Basic Example

Select records with common customers in both input file and reference file. Save unmatched records to a separate file oth.csv.

$ more dat1.csv
Customer,Quantity
A,1
B,2
C,1
D,3
E,1
$ more ref1.csv
Customer,Gender
A,Female
B,Male
E,Female
$ mcommon k=Customer m=ref1.csv u=oth.csv i=dat1.csv o=rsl1.csv
#END# kgcommon i=dat1.csv k=Customer m=ref1.csv o=rsl1.csv u=oth.csv
$ more rsl1.csv
Customer%0,Quantity
A,1
B,2
E,1
$ more oth.csv
Customer%0,Quantity
C,1
D,3

Example 2: Select unmatched records from the input file

Reverse selection criteria by using the -r option, the "Customer" not in the reference file are selected.

$ mcommon k=Customer m=ref1.csv -r i=dat1.csv o=rsl2.csv
#END# kgcommon -r i=dat1.csv k=Customer m=ref1.csv o=rsl2.csv
$ more rsl2.csv
Customer%0,Quantity
C,1
D,3

Example 3: Different names of join key

If the join key field name in the reference file is different, specify the field name at ¥verb|K=|.

$ more ref2.csv
CustomerID,Gender
A,Female
B,Male
E,Female
$ mcommon k=Customer K=CustomerID i=dat1.csv m=ref2.csv o=rsl3.csv
#END# kgcommon K=CustomerID i=dat1.csv k=Customer m=ref2.csv o=rsl3.csv
$ more rsl3.csv
Customer%0,Quantity
A,1
B,2
E,1

Example 4: Example with duplicate key fields

Record selection with duplicate records in both input file and reference file.

$ more dat3.csv
Customer,Quantity 
A,1
A,2
A,3
B,1
D,1
D,2
$ more ref3.csv
Customer
A
A
D
$ mcommon k=Customer m=ref3.csv -r i=dat3.csv o=rsl4.csv
#END# kgcommon -r i=dat3.csv k=Customer m=ref3.csv o=rsl4.csv
$ more rsl4.csv
Customer%0,Quantity 
B,1

Related commands

mselstr : This command can be used when the types of join key in reference file is not a lot.

mnrcommon : This command can be used when the join key in the reference file is not unique.

mjoin : This command is not only used for selecting data, but also for combining fields.