field compares

grozniy · #1 (**permalink**) 05-02-07, 12:19

I have a file with records. Records are either IN or OUT

I need to compare field $3 in IN record to filed $14 in OUT record and if they match output it to a file, otherwise ignore.

Fileds are separated by commas.

Can anyone please help?

stolze · #2 (**permalink**) 05-04-07, 03:49

I'm not sure I understood what you want to do. If you want to compare field $3 with $14 in the same record, then a simple AWK script could do this:

Code:

awk -F , '{ if ( $3 == $14 ) { print $3 } }' < file

grozniy · #3 (**permalink**) 05-04-07, 09:37

Quote:

Originally Posted by stolze

I'm not sure I understood what you want to do. If you want to compare field $3 with $14 in the same record, then a simple AWK script could do this:

Code:

awk -F , '{ if ( $3 == $14 ) { print $3 } }' < file

Yes, but I want to compare two fields of two different records.

Sort of like an SQL query

Select in.field1, in.field2, out.field3
from in, out
where in.field4 = out.field5;

stolze · #4 (**permalink**) 05-04-07, 10:24

I think you should give a better description of the use case. What exactly do you want to achieve? Also, could you provide a few more details like:
- What's your IN and OUT thing? Files?
- Are the records numbered in a certain way or don't you have any ordering?
- Are 4th and 13th field fixed or do you want to have that parameterized?
- Do you want to compare each record in IN with each record in OUT and return information from matching records only?
- In your example, you have a cross join. Are you possibly interested in outer joins, too.

grozniy · #5 (**permalink**) 05-04-07, 10:28

Quote:

Originally Posted by stolze

Could you provide a few more details like:
- What's your IN and OUT thing? Files?
- Are the records numbered in a certain way or don't you have any ordering?
- Are 4th and 13th record fixed or do you want to have that parameterized?
- Do you just want to do a single comparison on one set of IN/OUT records or do you want to compare multiple records/fields in one shot? If the latter, which records do you want to compare?

Hey, thanks for your help. I am glad to provide you with examples

In, fname, lname, address, ssn
out, accountNum, date,time, ssn

This isn't the real data, but it is sufficient to see what I am trying to do

I want to compare the SSN in the In to the SSN in the out to get the account number.

pdreyer · #6 (**permalink**) 05-07-07, 04:11

Something like this?

Code:

cat f1
in, fname, lname, address, ssn
out, accountNum, date,time, ssn
in, fname3, lname3, address3, ssn3
in, fname2, lname2, address2, ssn2
out, accountNum2, date2,time2, ssn2
out, accountNum3, date3,time3, ssn3
out, accountNum5, date5,time5, ssn5
in, fname4, lname4, address4, ssn4

grep "^in" <f1 | sort -t"," -k 5,5 >f2

grep "^out" <f1 | sort -t"," -k 5,5 | join -t"," -1 5 -2 5 -a 1 -a 2 -e " NULL" -o 1.2,1.3,2.2 f2 -
 fname, lname, accountNum
 fname2, lname2, accountNum2
 fname3, lname3, accountNum3
 fname4, lname4, NULL
 NULL, NULL, accountNum5

rm f2

Internet Services

Web Development

Digital Marketing

Consumer Tech