Vipin Choudhary
Vipin Choudhary

Reputation: 341

File merge on the basis of multiple column match

config

service1,208000,rule1
service2,308000,rule2

file1

value1,value2,service1|servicex|service2,value3,value4,208000
value1,value2,service1,value3,value4,208000
value1,value2,service1,value3,value4,
value1,value2,servicex,value3,value4,208000
value1,value2,service2,value3,value4,308000
value1,value2,service2,value3,value4,308000
value1,value2,service3,value3,value4,408000

output

value1,value2,service1|servicex|service2,value3,value4,208000,rule1
value1,value2,service1,value3,value4,208000,rule1
value1,value2,service1,value3,value4,,NORULEMATCHING
value1,value2,servicex,value3,value4,208000,NORULEMATCHING
value1,value2,service2,value3,value4,308000,rule2
value1,value2,service2,value3,value4,308000,rule2
value1,value2,service3,value3,value4,408000,NORULEMATCHING

I have Tried the Following:

awk -f myawk.awk config file1

where myawk.awk is

BEGIN { FS = OFS = "," }
FNR == NR {
  servicename=$1;
  qos=$2;
  $1 = "";
  $2 = "";
  a[servicename,qos] = $0;
  next
}
{
  split($3,plan,"|");
  if(plan[1]","$6 in a){
        split(a[plan[1]","$6],ruledesc,",");
        $7 = ruledesc[3];
        print $0;
  }
  else {
        $7 = NORULEMATCHING;
        print $0;
  }
}

Note:
- From config file, we need to match column1 and column 2 (combined as primary key) from file1's column3 (first field after FD as a pipe)and column 5
- If it matches then insert column3 of config file in 7th column in output

Challenge I am facing is creating an array on the basis of 2 columns.

Upvotes: 2

Views: 87

Answers (1)

Håkon Hægland
Håkon Hægland

Reputation: 40778

You can try:

awk -f myawk.awk config file1

where myawk.awk is:

BEGIN { FS = OFS = "," }
FNR == NR {
    a[$1,$2] = $3
    next
}
{
    split($3,plan,"|")
    key=plan[1] SUBSEP $6
    if( key in a){
        $7 = a[key]
        print $0
    } else {
        $7 = "NORULEMATCHING"
        print $0
    }
}

Upvotes: 1

Related Questions