3.36 mnrcommon - Select Records within Specified Range(s) from Reference File

Select the record in the input file that matches the records within the defined range(s) defined from the reference file. k= parameter specifies the key field name from the input file to match with the key defined in K= from the reference file. The selection criteria is based on the data series from the input file defined in r= parameter for records that falls within the data range in the reference file defined in the R= parameter. Add %n after the item name if the field defined at r= parameter is a numerical value.

Format

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

Parameters

`k=`	Key item(s) to match in the input file (Multiple keys can be specified).
	The key(s) specified will be matched with the key field(s) at `K=` parameter from the reference file.
`m=`	Specify name of reference file.
	Data is read from standard input if this parameter is not defined. (when i= is specified)
`R=`	Define the value range (start,end) in the reference file.
	If the first argument is NULL means less than, if the second argument is NULL means more than.
`r=`	Field name of input file for range comparison. [%n]
	Records in the input file that matches the key field specified in the `k=` parameter in the reference data is selected.
	when processing as numeric value, %n will be added to field name defined at `r=` parameter.
`K=`	Key field(s) in the reference data for matching (Multiple keys can be specified)
	The key specified will be matched with the key field defined in `k=` parameter from the input file.
	Records in the input file that matches the key field specified in the `k=` parameter in the reference data is selected.
`u=`	Write unmatched records to this output file.
`-r`	Reverse selection
	Select records that is not within the data range defined at `R=` parameter.

Sort Criteria

Fields specified at r=,R= must be sorted beforehand. However, the numerical values defined in r=,R= should be sorted in ascending order to join with the numerical range. Where k=,K= is specified, the strings defined at the parameter must be sorted in ascending order.

For example, when the parameter k=key K=Key r=val%n R=range i=dat.csv m=ref.csv is specified, dat.csv data, should be sorted with msortf f=key,val%n as the criteria, and ref.csv data, should be sorted with msortf f=Key,range%n as the criteria.

Examples

Example 1: Basic Example

Select records where the transaction date is 20080203 with transaction "Amount" greater than 5 and less than 15 or greater than 40 and less than 50.

$ more dat1.csv
Date,Amount
20080123,10
20080203,10
20080203,20
20080203,45
200804l0,50
$ more ref1.csv
Date,AmountF,AmountT
20080203,5,15
20080203,40,50
$ mnrcommon k=Date m=ref1.csv R=AmountF,AmountT r=Amount%n i=dat1.csv o=rsl1.csv
#END# kgnrcommon R=AmountF,AmountT i=dat1.csv k=Date m=ref1.csv o=rsl1.csv r=Amount%n
$ more rsl1.csv
Date%0,Amount
20080203,10
20080203,45

Example 2: Reverse selection

Add -r option to reverse selection criteria.

$ mnrcommon k=Date m=ref1.csv R=AmountF,AmountT r=Amount%n -r i=dat1.csv o=rsl2.csv
#END# kgnrcommon -r R=AmountF,AmountT i=dat1.csv k=Date m=ref1.csv o=rsl2.csv r=Amount%n
$ more rsl2.csv
Date%0,Amount
20080123,10
20080203,20
200804l0,50

Related commands

mcommon : Select common records in reference file

mnrjoin : Natural join data from the reference file with multiple ranges.