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

Tags
shell scripts

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 05: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 08:38 AM
awk to update field in file2 if not the same as file1 cmccabe Shell Programming and Scripting 4 01-04-2017 11: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 10: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 06:14 AM
Retreive the records from file2 by using the first field in file1 i150371485 Shell Programming and Scripting 4 10-18-2012 09:11 AM
Plz Help. Compare 2 files field by field and get the output in another file. i150371485 Shell Programming and Scripting 5 07-26-2012 01:32 AM
Compare two files Field by field and output the result in another file i150371485 Shell Programming and Scripting 7 07-20-2012 08:01 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 02: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 09:07 PM
using field 2 in file2 to complete field 3 in file1 smarones Shell Programming and Scripting 8 11-04-2011 07:33 AM
Read Field from file1 and find and replace in file2 gdevadas Shell Programming and Scripting 8 04-07-2011 06:28 PM
Awk Compare File1 File2 on f2 RacerX Shell Programming and Scripting 4 10-27-2008 10:50 AM
how do i pattern match a field with awk? someone123 Shell Programming and Scripting 4 06-03-2008 09:08 AM
Awk Compare f1,f2,f3 of File1 with f1 of File2 RacerX Shell Programming and Scripting 6 11-09-2007 01:34 AM


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

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