INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Compare two fields and create a third

Compare two fields and create a third

(OP)
I have two files that I would like to lookup id's for and add their corresponding values to the end of a master file. I can do this in Microsoft Access, but don't really want to.

The office file has a structure of Office ID|Office Name|Address|City|... etc.
The agent file has a structure of Office ID|Agent ID|First Name|Last Name|Address|City|... etc.

The office ids are unique.
The agent ids are not unique numbers

I got a script from Annihilannic a long time ago but it doesn't work with this particular situation.

Masterfile.txt (these field positions are not absolute)
xxx|xxx|xxx|123|xxx|xxx|567|xxx|xxx|
xxx|xxx|xxx|999|xxx|xxx|567|xxx|xxx|

Sample data for office (office.txt)
Office ID|Office Name|Office Address|...
123|Starbucks|123 Anywhere St|....
999|McDonalds|456 Main st|...

Sample data for agent (agent.txt)
Office ID|Agent ID|First Name|Last Name|...
123|567|Tom|Sawyer|...
999|567|Huck|Finn|...

here is what I have been using to match agent id's and appending the actual agent name to the end of the file.

awk -F'|' '
FILENAME==ARGV[1] { firstname[$1]=$2 }
FILENAME==ARGV[2] { print $0 firstname[$14] "|" }
' agent.txt masterfile.txt > masterfile_temp.txt

That statement works perfect when the agent ids are unique but put the first agent id it comes across in the file, ignoring any other matches

So my new output would be.
xxx|xxx|xxx|xxx|Starbucks|Tom|Sawyer|
xxx|xxx|xxx|xxx|McDonalds|Huck|Finn|

Any help would be greatly appreciated.




RE: Compare two fields and create a third

I presume the agent IDs are unique for each office IDs, in which case you could use both of the keys to your arrays, e.g.

CODE

FILENAME==ARGV[1] { firstname[$1,$2] = $3 } 

I'm a bit confused though because the field numbers in the sample code you posted don't correspond with the sample data.

Annihilannic
tgmlify - code syntax highlighting for your tek-tips posts

RE: Compare two fields and create a third

(OP)
you are correct, the agent id's are unique for each office id.

the firstname[$14] is the actual position in the masterfile of the agent id. sorry not like in the example
this is what I came up with but the field is empty in mastefile_temp.txt
basically I'm just changing the array built up based on your example

# insert First Name
awk -F'|' '
FILENAME==ARGV[1] { firstname[$1,$2] = $3 }
FILENAME==ARGV[2] { print $0 firstname[$14] "|" }
' agent.txt mastefile.txt > masterfile_temp.txt

RE: Compare two fields and create a third

Since you are now creating an array with multiple indices, you also need to look up the array using multiple indices. I'm assuming masterfield has the office id in there somewhere as well, let's say field $13 for example, in which case you need to use print $0 firstname[$13,$14].

Annihilannic
tgmlify - code syntax highlighting for your tek-tips posts

RE: Compare two fields and create a third

(OP)
that got it thank you. I really wish I could wrap my head around arrays for things like this. Do you have a url or a book you could recommend, preferably a dummies version. thank you very much

here is what I did

# insert First Name
awk -F'|' '
FILENAME==ARGV[1] { firstname[$1,$2] = $3 }
FILENAME==ARGV[2] { print $0 firstname[$14,$15] "|" }
' agent.txt masterfile.txt > masterfile_temp.txt
mv -f masterfile_temp.txt masterfile.txt
#
# insert Last Name
awk -F'|' '
FILENAME==ARGV[1] { lastname[$1,$2] = $4 }
FILENAME==ARGV[2] { print $0 lastname[$14,$15] "|" }
' agent.txt mastefile.txt > masterfile_temp.txt
mv -f masterfile_temp.txt masterfile.txt

RE: Compare two fields and create a third

Might as well do in one step:

CODE

# insert First Name
awk -F'|' '
FILENAME==ARGV[1] { firstname[$1,$2] = $3; lastname[$1,$2] = $4 }
FILENAME==ARGV[2] { print $0 firstname[$14,$15] "|" lastname[$14,$15] "|" }
' agent.txt masterfile.txt > masterfile_temp.txt
mv -f masterfile_temp.txt masterfile.txt 

The gawk documentation is excellent, here is the part describing arrays.

Annihilannic
tgmlify - code syntax highlighting for your tek-tips posts

RE: Compare two fields and create a third

(OP)
perfect, thank you again.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close