Compare two files with awk

[Chris Frangandonis]

Hi All,

Help needed for the following problem I have.

I have two files:
FILE A
========
0113434343 SJJK:01
0113434342 SJJK:01
0113434346 SJJK:01
0126652131 JGGG:01
0126652133 JGGG:01
0126652137 JGGG:01
0214020000 BGBG:01
0214020001 BGBG:01
0214020002 BGBG:01

FILE B
=======
258826955840 0113434343 10 V rjs+:01 MPO7DB
26465220052 0126652131 70 V rjs+:01 WKI7DB
258847585067 0730720260 540 V rjs+:01 VMO7DB
474892345 002215548734 5570 V rjs+:01 TSALOG
26771377193 0026391846469 1600 V rjs+:01 FTN7DB
258826953840 0113434343 30 V rjs+:01 MPO7DD
2348037059676 07313166266 0 U rjs+:01 BXF7CB
865966104371 0026391918459 1210 V rjs+:01 BE27TB
255555555550 0214020000 1220 V rjs+:01 AWP7CB
865966104370 0026391374035 0 U rjs+:01 BE27TB
255526953840 0113434343 30 U rjs+:01 MPO7BB
118346993 002348027245963 8430 V rjs+:01 TSALOG
255555555550 0214020000 0 U rjs+:01 AWP7CB
255526953840 0214020000 20 V rjs+:01 AWP7CB
DEF 0791407394 0 U rjs+:01 VLG7TB

Use FILE A colum $1 to check in FILE B colum $2 and if true look at colum $4 if the colum is ="V",take
into consideration the $3. Continue the pattern match. If another match in $2 and $4 == "V" add the result
from the first match with $3 to this match as well as number of occurrences for e.g "2". If match $2
and $4=="U" then add $3 and increment count=1.

My Code So far
==============
awk 'FNR==NR {
t[$1]=$2;f[$2]=0;next
}
$2 in t {
if($4=="V"){
print t[$2],++f[$2],$4
}
else
if($4=="U"){
print t[$2],++f[$2],$4
}
}' First Second

Output File should look like below


RESULT
======
NAME COUNTS STATUS DURATION
SJJK:01 2 V 40 (note this is for 0113434343)
SJJK:01 1 U 10 (note this is for 0113434343)
JGGG:01 1 V 70 (note this is for 0126652131)
BGBG:01 2 V 1240 (note this is for 0214020000)
BGBG:01 1 U 0 (note this is for 0214020000)

Hope this helps

Many Thanks
Chris

[Hein van den Heuvel · Accepted solution]

The challenge seems not as much a 'compare', but a 'join'.

SQL is good at this stuff.
Perl is. Awk can do, but is more tedious.

The proper solution depends a little on table size, but assuming table A is a few MB or less, try this:

use strict;
my (%a_table, %duration, %counts);
open A, "while () {
my ($number,$name) = split;
$a_table{$number}=$name;
}
close A;
open B, "while () {
my ($x,$number,$duration,$status) = split;
next unless $a_table{$number};
$duration{$number.$status}+=$duration;
$counts{$number.$status}++;
}
close B;
foreach (sort keys %counts) {
my $status = chop;
printf ("%10s %5d %s %6d\n",
$a_table{$_}, $counts{$_.$status}, $status, $duration{$_.$status});
}

Hei...