👤
Home Man
Search
Today's Posts
Register

BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

AWK: Pattern match between 2 files, then compare a field in file1 as > or < field in file2

👤 Login to reply

 
Thread Tools Search this Thread
# 1  
Old 10-06-2011
AWK: Pattern match between 2 files, then compare a field in file1 as > or < field in file2

First, thanks for the help in previous posts... couldn't have gotten where I am now without it!

So here is what I have, I use AWK to match $1 and $2 as 1 string in file1 to $1 and $2 as 1 string in file2. Now I'm wondering if I can extend this AWK command to incorporate the following:

If $1 and $2 in file1 equals $1 and $2 in file2, then return the the values of $1, $2 and $4 in file1 IF $5 in file1 is greater than $3 in file2

I could put a loop together but I think that would be very inefficient compared to "if" this AWK command could be extended.

file1
Code:
 
PRODCON1|SYSTEM.IOA|040720101327|USER1|201004071327
PRODCON2|SYSTEM.TEST|040720101327|USER1|201110010932
SA|SYSTEM.CCR|040720101327|USER1|201004071327
US|SYSTEM.PPD|040720101327|USER1|201004071327
VP|SYSTEM.IOA|040720101327|USER1|201110012346

file2
Code:
 
PRODCON1|SYSTEM.IOA|201109201128
PRODCON2|SYSTEM.TEST|201109201128
SA|SYSTEM|201109201128
US|SYSTEM|201109201128
VP|SYSTEM.IOA|201109201128

First I match $1 and $2 in both files with this:
Code:
awk -F\| 'NR == FNR {i[$1, $2]; next}(($1, $2) in i )' file2 file1

The result contains only the lines in file1 where $1 and $2 are the same in both files:
Code:
 
PRODCON1|SYSTEM.IOA|040720101327|USER1|201004071327
PRODCON2|SYSTEM.TEST|040720101327|USER1|201110010932
VP|SYSTEM.IOA|040720101327|USER1|201110012346

Now, from these results, I need to determine if $5 in the output above (which is date format YYYYMMDDHHmm) is greater than $3 in file2 and display $1, $2, and $4 from file1 where this conditions exists:

Required final output:
Code:
 
PRODCON2|SYSTEM.TEST|USER1
VP|SYSTEM.IOA|USER1

# 2  
Old 10-06-2011
Hi right_coaster,

Try:

Code:
$ cat file1
PRODCON1|SYSTEM.IOA|040720101327|USER1|201004071327
PRODCON2|SYSTEM.TEST|040720101327|USER1|201110010932
SA|SYSTEM.CCR|040720101327|USER1|201004071327
US|SYSTEM.PPD|040720101327|USER1|201004071327
VP|SYSTEM.IOA|040720101327|USER1|201110012346
$ cat file2
PRODCON1|SYSTEM.IOA|201109201128
PRODCON2|SYSTEM.TEST|201109201128
SA|SYSTEM|201109201128
US|SYSTEM|201109201128
VP|SYSTEM.IOA|201109201128
$ awk -F\| 'NR == FNR {i[$1, $2] = $3; next} { if ( i[$1, $2] && i[$1, $2] < $5 ) { printf( "%s|%s|%s\n", $1, $2, $4 ) } }' file2 file1
PRODCON2|SYSTEM.TEST|USER1
VP|SYSTEM.IOA|USER1

Regards,
Birei
The Following User Says Thank You to birei For This Useful Post:
right_coaster (10-06-2011)
# 3  
Old 10-06-2011
Awesome, works nicely... and I'll run it on my 12,000 line files and see how it goes. Thanks again.
# 4  
Old 10-06-2011
Quote:
Originally Posted by birei
Hi right_coaster,

Try:

Code:
$ cat file1
PRODCON1|SYSTEM.IOA|040720101327|USER1|201004071327
PRODCON2|SYSTEM.TEST|040720101327|USER1|201110010932
SA|SYSTEM.CCR|040720101327|USER1|201004071327
US|SYSTEM.PPD|040720101327|USER1|201004071327
VP|SYSTEM.IOA|040720101327|USER1|201110012346
$ cat file2
PRODCON1|SYSTEM.IOA|201109201128
PRODCON2|SYSTEM.TEST|201109201128
SA|SYSTEM|201109201128
US|SYSTEM|201109201128
VP|SYSTEM.IOA|201109201128
$ awk -F\| 'NR == FNR {i[$1, $2] = $3; next} { if ( i[$1, $2] && i[$1, $2] < $5 ) { printf( "%s|%s|%s\n", $1, $2, $4 ) } }' file2 file1
PRODCON2|SYSTEM.TEST|USER1
VP|SYSTEM.IOA|USER1

Regards,
Birei
a slight mod:
Code:
awk -F\| 'NR == FNR {i[$1, $2] = $3; next} i[$1, $2] && i[$1, $2] < $5 { print $1, $2, $4 }' OFS='|' file2 file1

The Following User Says Thank You to vgersh99 For This Useful Post:
birei (10-06-2011)
# 5  
Old 10-06-2011
Using either of these methods, is it possible to print a field from file2?

NOTE: I added printing $5 below to get the date from file1 and if at all possible I'd like to print the date ($3) from file2 at the end. I tried to reference back to it with the i variable but it keeps bombing.

Code:
awk -F\| 'NR == FNR {i[$1, $2] = $3; next} i[$1, $2] && i[$1, $2] < $5 { print $1, $2, $4, $5 }' OFS='|' file2 file1
PRODCON2|SYSTEM.TEST|USER1|201110010932
VP|SYSTEM.IOA|USER1|201110012346

Desired: (data is from file1 except last entry is $3 from file2)
Code:
PRODCON2|SYSTEM.TEST|USER1|201110010932|201109201128
VP|SYSTEM.IOA|USER1|201110012346|201109201128

---------- Post updated at 06:07 PM ---------- Previous update was at 05:34 PM ----------

Aha! / Duh! moment... got it, thanks again for the helpful posts.

Code:
 
awk -F\| 'NR == FNR {i[$1, $2] = $3; next} i[$1, $2] && i[$1, $2] < $5 { print $1, $2, $4, $5, i[$1, $2] }' OFS='|' file2 file1

The Following User Says Thank You to right_coaster For This Useful Post:
vgersh99 (10-06-2011)
👤 Login to reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
awk to look up values in File 2 from File 1, & printingNth field of File1 based value of File2 $2 samonl Shell Programming and Scripting 5 03-04-2018 04:37 PM
awk to update field using matching value in file1 and substring in field in file2 cmccabe Shell Programming and Scripting 2 06-18-2017 07:38 AM
awk to update field in file2 if not the same as file1 cmccabe Shell Programming and Scripting 4 01-04-2017 10:08 AM
awk to search field2 in file2 using range of fields file1 and using match to another field in file1 cmccabe Shell Programming and Scripting 6 12-22-2016 09:55 AM
Replacing first field of file2 with the second filed of file1 for matching cases ailnilanjan Shell Programming and Scripting 6 11-06-2014 05:14 AM
Retreive the records from file2 by using the first field in file1 i150371485 Shell Programming and Scripting 4 10-18-2012 08:11 AM
Compare a common field in two files and append a column from File 1 in File2 Santoshbn Shell Programming and Scripting 2 04-26-2012 01:12 AM
Match pattern in a field, print pattern only instead of the entire field lucasvs UNIX for Dummies Questions & Answers 18 03-05-2012 08:07 PM
using field 2 in file2 to complete field 3 in file1 smarones Shell Programming and Scripting 8 11-04-2011 06:33 AM
Read Field from file1 and find and replace in file2 gdevadas Shell Programming and Scripting 8 04-07-2011 05:28 PM


All times are GMT -4. The time now is 11:11 PM.

Unix & Linux Forums Content Copyright©1993-2018. All Rights Reserved.
×
UNIX.COM Login
Username:
Password:  
Show Password