Visit Our UNIX and Linux User Community


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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting AWK: Pattern match between 2 files, then compare a field in file1 as > or < field in file2
# 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
This User Gave Thanks to birei For This Post:
# 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

This User Gave Thanks to vgersh99 For This Post:
# 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

This User Gave Thanks to right_coaster For This Post:

Previous Thread | Next Thread
Test Your Knowledge in Computers #221
Difficulty: Easy
Apple's OS X (macOS) installed base had grown to 80 million users as reported at the WWDC 2014.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to look up values in File 2 from File 1, & printingNth field of File1 based value of File2 $2

I have two files which are the output of a multiple choice vocab test (60 separate questions) from 104 people (there are some missing responses) and the question list. I have the item list in one file (File1) Item,Stimulus,Choice1,Choice2,Choice3,Choice4,Correct... (5 Replies)
Discussion started by: samonl
5 Replies

2. Shell Programming and Scripting

awk to update field using matching value in file1 and substring in field in file2

In the awk below I am trying to set/update the value of $14 in file2 in bold, using the matching NM_ in $12 or $9 in file2 with the NM_ in $2 of file1. The lengths of $9 and $12 can be variable but what is consistent is the start pattern will always be NM_ and the end pattern is always ;... (2 Replies)
Discussion started by: cmccabe
2 Replies

3. Shell Programming and Scripting

awk to update field in file2 if not the same as file1

Trying to use awk to: update $2 in file2 with the $2 value in file1, if $1 in file1 matches $13 in file2, which is tab-delimeted. The $2values may already be the same so in that case nothing happens and the next line is processed. There are exactly 4,605 unique $13 values. Thank you :). ... (4 Replies)
Discussion started by: cmccabe
4 Replies

4. Shell Programming and Scripting

awk to search field2 in file2 using range of fields file1 and using match to another field in file1

I am trying to use awk to find all the $2 values in file2 which is ~30MB and tab-delimited, that are between $2 and $3 in file1 which is ~2GB and tab-delimited. I have just found out that I need to use $1 and $2 and $3 from file1 and $1 and $2of file2 must match $1 of file1 and be in the range... (6 Replies)
Discussion started by: cmccabe
6 Replies

5. Shell Programming and Scripting

Replacing first field of file2 with the second filed of file1 for matching cases

Dear All, Need your help..:D I am not regular on shell scripts..:( I have 2 files.. Content of file1 cellRef 4};"4038_2_MTNL_KALAMBOLI" cellRef 1020};"4112_3_RAINBOW_BLDG" cellRef 134};"4049_2_TATA_HOSPITAL" cellRef 1003};"4242_3_HITESH_CONSTRUCTION" cellRef... (6 Replies)
Discussion started by: ailnilanjan
6 Replies

6. Shell Programming and Scripting

Retreive the records from file2 by using the first field in file1

Hi Freinds, i have a file1 as below file1 1|ndmf|fdd|d3484|34874 2|jdehf|wru7|478|w489 3|dfkj|wej|484|49894 file2 contains lakhs of records and not in sorted order i want to retrive only the records from file2 by searcing the first field of file 1 i used grep ^1 file2... (4 Replies)
Discussion started by: i150371485
4 Replies

7. Shell Programming and Scripting

Compare a common field in two files and append a column from File 1 in File2

Hi Friends, I am new to Shell Scripting and need your help in the below situation. - I have two files (File 1 and File 2) and the contents of the files are mentioned below. - "Application handle" is the common field in both the files. (NOTE :- PLEASE REFER TO THE ATTACHMENT "Compare files... (2 Replies)
Discussion started by: Santoshbn
2 Replies

8. UNIX for Dummies Questions & Answers

Match pattern in a field, print pattern only instead of the entire field

Hi ! I have a tab-delimited file, file.tab: Column1 Column2 Column3 aaaaaaaaaa bbtomatoesbbbbbb cccccccccc ddddddddd eeeeappleseeeeeeeee ffffffffffffff ggggggggg hhhhhhtomatoeshhh iiiiiiiiiiiiiiii ... (18 Replies)
Discussion started by: lucasvs
18 Replies

9. Shell Programming and Scripting

using field 2 in file2 to complete field 3 in file1

Hello, I was hoping someone could help me with this work related problem... basically what I want to do is the following: file2: 1 o 2 t 4 f 5 v 7 n 8 e 10 a file1: 1 : (8 Replies)
Discussion started by: smarones
8 Replies

10. Shell Programming and Scripting

Read Field from file1 and find and replace in file2

Hi All, I have file1 line below: $myName$|xxx Now I need to read the file1 and find for $myName$ in file2 and replace with xxx file1: $myName$|xxx file2: My name is $myName$ expected output in file2 after executing the script is below: my name is xxx Thanks, (8 Replies)
Discussion started by: gdevadas
8 Replies

Featured Tech Videos