Set of 2 records as one unit

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Set of 2 records as one unit
# 1  
Old 09-25-2011
Set of 2 records as one unit

Hi Experts,

back at this forum again. Have a tab separated file like this ---
ACCNN          AMT(E/$)       TYPE       ID
11233            23.20($)         AUTH    339
11233            19.00($)         FINAL    339   

11234            349.84($)         AUTH    42332  
11234            349.84($)         FINAL    42332  

11235            7.4(E)              AUTH    800

11236            445.74($)         AUTH    080

11238            763.94($)         AUTH     843883
11238            763.94($)         FINAL     843883

11234            37.43($)           AUTH     11201

Here is the question I have at hand(this data is from a Credit card transaction log), I need to find out what are the various Accounts that have the Authorization and Settlement receipts with the same Transaction id(the last column). In other words, I want to see the following result from the above file -
11233            23.20($)         AUTH    339
11233            19.00($)         FINAL    339   

11234            349.84($)         AUTH    42332  
11234            349.84($)         FINAL    42332  

11238            763.94($)         AUTH     843883
11238            763.94($)         FINAL     843883

So, I have to find out what is the value of the first column and then compare that value with another sets of rows and find out which one matches and then see if the value of the first column are the same along with the value of the last column.

Here is what I did on this one. All I could do is sort the records out based on Column1 and Column4. After that point, I dont know how to go ahead with the rest and I look like a fool to myself.

Please help.


Last edited by Scott; 09-25-2011 at 06:11 AM.. Reason: Code tags, please...
# 2  
Old 09-25-2011
nawk '{A[$1":"$NF]+=1;B[$0]=$1":"$NF}END{for(i in B) if (A[B[i]]>1) print i}' myfile

This User Gave Thanks to ctsgnb For This Post:
# 3  
Old 09-25-2011

Amazing. Perfect results. Ran this even on a file with 200,000 rows and came back at me under 1 minute.

Big big thanks.

Btw, can you please please decipher this for me as I might have to extrapolate this for maybe 4 rows instead of 2 as I mentioned in here & as such, some explanation would be very timely.


Last edited by PG3; 09-25-2011 at 06:21 AM.. Reason: Making myself clearer
# 4  
Old 09-25-2011
Alternatively (assuming ID's are unique so accountnumbers are unique too):
awk '/AUTH/{A[$NF]=$0}/FINAL/&&A[$NF]{print $0 RS A[$NF]}' infile

or more memory efficient:
awk '/AUTH/{A[$NF]=$0}/FINAL/&&A[$NF]{print $0 RS A[$NF];delete A[$NF]}' infile

Last edited by Scrutinizer; 09-25-2011 at 06:58 AM..
This User Gave Thanks to Scrutinizer For This Post:
# 5  
Old 09-25-2011

Just great ! but I couldn't follow you as well.

Im simply embarrassed to even pose this question to you. Can you please explain this to me as to what you are doing here ?

# 6  
Old 09-25-2011
I'll give it a try:

/AUTH/{A[$NF]=$0}If a line contains AUTH then store that line ($0) in an array element with index of the ID in the last column ($NF)
/FINAL/ && A[$NF] If a line contains FINAL and an array element exists with the same ID ($NF)
{print $0 RS A[$NF]}Then print that line and the array element separated by a newline (RS)
Probably you'll prefer to flip the lines around:
awk '/AUTH/{A[$NF]=$0}/FINAL/&&A[$NF]{print A[$NF] RS $0}' infile

Last edited by Scrutinizer; 09-25-2011 at 07:59 AM.. Reason: Removed superfluous ]
# 7  
Old 09-25-2011

Thanks a bunch for making yourself clear in you are doing here but I dont get it completely. I think I should pick up the Kernighan's book on Awk.

First things first, "Thanks for the help".

At any rate, 2 new challenges. When I run the newly pasted, line, it doesnt run and errors with the following message and Im pasting directly over here. Im running bash btw.

Also, how do I run this same line for 3 RECORDS not 2 RECORDS and here is the real business scenario for this. Lets say the transaction is coming from a restaurant, it has initial authorization amount as Authorized amount and then the customer lets say is nice enough to add a gratuity(tip) to it. So when the merchant presents the transaction to the bank, then the final amount would be different from amount Authorized. So, how do I reconcile these three sets of records ?

Please advise.

/Users/PGonzalez 02:53:07 $awk '/AUTH/{A[$NF]=$0}/FINAL/&&A[$NF]{print $0 RS A[$NF];delete A[$NF]}' ADCC
11233 19.00($) FINAL 339 
11233 23.20($) AUTH 339
11234 349.84($) FINAL 42332 
11234 349.84($) AUTH 42332 
11238 763.94($) FINAL 843883
11238 763.94($) AUTH 843883
/Users/PGonzalez 02:54:33 $
/Users/PGonzalez 02:54:43 $awk '/AUTH/{A[$NF]=$0}/FINAL/&&A[$NF]{print A[$NF] RS $0]}' ADCC
awk: extra ] at source line 1
 context is
	/AUTH/{A(NF)]=$0}/FINAL/&&A(NF)]{print A(NF)] RS >>>  $0] <<< 
awk: syntax error at source line 1
awk: illegal statement at source line 1
	extra ]
/Users/PGonzalez 03:26:35 $

Last edited by Scott; 09-25-2011 at 08:31 AM.. Reason: Code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Simple unit script

Hello guys this is my first post in this forum. Since now ive been passive an ive only been looking for existing information. Now I could use specific help on a UNIX script i want to make that would: 1. Take 1-3 arguments. 2. Display the contents of its arguments, formatted as follows:... (7 Replies)
Discussion started by: banzomaster
7 Replies

2. Shell Programming and Scripting

Compare two files with different number of records and output only the Extra records from file1

Hi Freinds , I have 2 files . File 1 |nag|HYd|1|Che |esw|Gun|2|hyd |pra|bhe|3|hyd |omu|hei|4|bnsj |uer|oeri|5|uery File 2 |nag|HYd|1|Che |esw|Gun|2|hyd |uer|oi|3|uery output : (9 Replies)
Discussion started by: i150371485
9 Replies

3. HP-UX

Load average unit

Hi, On load average graph, unit is 100m, 200m, 300...800m. I don't understand what it means. Thx for helping (3 Replies)
Discussion started by: Michenux
3 Replies

4. Shell Programming and Scripting

Adding existing set of records in the same file

I have a file with 50,000 records in it, i have a requirement to use the same 50,000 records and add them 4 times to the same file to make a total of 200,000 records. I was wondering how to do this using ksh. Any help is greatly appreciated. (2 Replies)
Discussion started by: vpv0002
2 Replies

5. Shell Programming and Scripting

extract set of matching records

i have a pipe delimited file with records spread in many lines. i need to extract those records 1)having X in beginning of that record 2)and having at least one Y in beginning before other record begins eg: X|Rec1| A|Rec1| Y|Rec1| X|Rec2| Y|Rec2| Z|Rec3| X|Rec4| M|Rec4| ... (4 Replies)
Discussion started by: finder255
4 Replies

6. Shell Programming and Scripting

Based on num of records in file1 need to check records in file2 to set some condns

Hi All, I have two files say file1 and file2. I want to check the number of records in file1 and if its atleast 2 (i.e., 2 or greater than 2 ) then I have to check records in file2 .If records in file2 is atleast 1 (i.e. if its not empty ) i have to set some conditions . Could you pls... (3 Replies)
Discussion started by: mavesum
3 Replies

7. UNIX for Dummies Questions & Answers

seperating records with numbers from a set of numbers

I have two files one (numbers file)contains the numbers(approximately 30000) and the other file(record file) contains the records(approximately 40000)which may or may not contain the numbers from that file. I want to seperate the records which has the field 1=(any of the number from numbers... (15 Replies)
Discussion started by: Shiv@jad
15 Replies

8. Shell Programming and Scripting

Getting MAC from GPS unit

Never mind i got the answer thanks., (0 Replies)
Discussion started by: deaconf19
0 Replies

9. UNIX for Dummies Questions & Answers

Cutting a limited or given set of records in a datbase

I need to select "cut" a certain number of records in a database. ie. records 20 to 25. Help (4 Replies)
Discussion started by: Iaf_user
4 Replies
Login or Register to Ask a Question