Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Compare with 2 tables

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compare with 2 tables
# 1  
Old 06-12-2016
Compare with 2 tables
I have 3 file inputs,

file1
Code:
20160302|5485368299953|96|510101223440252|USA|5485368299953|6|800|2300|0
20160530|5481379883742|7|510101242850814|USA|5481379883742|5|540|2181|1500
20160513|5481279653404|24|510100412142433|INDIA|5481279653404|3|380|1900|0
20160112|5481367419640|146|510101245923316|INDIA|5481367419640|1|60|1500|3500
20160329|5481274428798|69|510100421020557|CHINA|5481274428798|3|380|1900|0
20160603|5482279814886|3|510101249024025|CHINA|5482279814886|5|200|1700|330
20160112|5482282312263|146|510101268030942|UK|5482282312263|5|200|1700|330
20160502|5482279817754|35|510100400128133|UEA|5482279817754|3|380|1900|599
20160120|5482281112440|138|510100421021084|UEA|5482281112440|2|150|1650|0

column 2nd (number of identity) in file 1 is the key

file2
Code:
00000000000000|OLD_CITIZEN|20160304
00000000000001|OLD_CITIZEN|20160305
00000000000002|OLD_CITIZEN|20160207
35568907300172|OLD_CITIZEN|20151219
00000000000004|OLD_CITIZEN|20151225
00000000000005|OLD_CITIZEN|20160214
35999905477238|OLD_CITIZEN|20160215
00000000000007|OLD_CITIZEN|20160301
00000000000008|OLD_CITIZEN|20151127
35284607292811|OLD_CITIZEN|20160301

column1 (security number) in file 2 shows number id of citizen

file3
Code:
5481274428798|35999905477238|ASUS|ZENFONE 2 ZE551ML|ORIGUT|ALASKA INCHARGE|YES|YES|YES|YES|YES|YES|20160501000000
54811636444|35420406027522|SAMSUNG|GALAXY S 8.4 LTE T705|ORIGUT|ALASKA OUTER|YES|YES|YES|YES|YES|YES|20160501000000
548116364444|35775905347060|BLACKBERRY|Q10|ORIGUT|ALASKA|YES|YES|YES|YES|YES|YES|20160506000000
5481274428798|35568907300172|APPLE|IPHONE 6S|ORIGUT|NULL|YES|YES|YES|YES|NO|YES|20160514160000
548116364442|35999905477239|ASUS|ZENFONE 2 ZE551ML|ORIGUT|ALASKA INCHARGE|YES|YES|YES|YES|YES|YES|20160514180000
5482279817754|NULL|NULL|NULL|ORIGUT|ALASKA INCHARGE|NULL|NULL|NULL|NULL|NULL|NULL|20160518020000
5481279653404|35284607292811|SAMSUNG|GALAXY J7 J700F DUOS|ORIGUT|NULL|YES|YES|YES|YES|YES|YES|20160521100000

column 1st is (number of identity) and column 2nd is (security number)

I expect my output tobe like this
Code:
20160302|5485368299953|96|510101223440252|USA|5485368299953|6|800|2300|0|NEW|1
20160530|5481379883742|7|510101242850814|USA|5481379883742|5|540|2181|1500|NEW|1
20160513|5481279653404|24|510100412142433|INDIA|5481279653404|3|380|1900|0|OLD|0
20160112|5481367419640|146|510101245923316|INDIA|5481367419640|1|60|1500|3500|NEW|1
20160329|5481274428798|69|510100421020557|CHINA|5481274428798|3|380|1900|0|OLD|0
20160603|5482279814886|3|510101249024025|CHINA|5482279814886|5|200|1700|330|NEW|1
20160112|5482282312263|146|510101268030942|UK|5482282312263|5|200|1700|330|NEW|1
20160502|5482279817754|35|510100400128133|UEA|5482279817754|3|380|1900|599|NEW|1
20160120|5482281112440|138|510100421021084|UEA|5482281112440|2|150|1650|0|NEW|1

if column 2nd in file 1 exist in file3 then check whether column 2nd in file 3 exist in file 2 or not. If it is exist then add "OLD|0". If it is not exist then add "NEW|1"

I did some process to have the output but thats too long and it takes time to run. Any awk to help?

Code:
awk 'NR==FNR {h[$2] = $1; next} {print $0,h[$2]}' file3 file1 > temp.txt
awk -F'|' 'NR==FNR {h[$1] = $2; next} {FS=OFS="|";print $0,h[$1]}' temp.txt file2.txt > xtemp.txt
awk -F'|' '{FS=OFS="|"}{if($12="OLD_CITIZEN") print $0,"OLD","0" };{if($12="") print $0,"NEW","1"}' > fin.txt

# 2  
Old 06-12-2016
Would that work?
Code:
awk -F\| 'FNR==1 {++fh} fh==1 {sn[$1]} fh==2 && $2 in sn {ni[$1]} fh==3 {print $0 ($2 in ni ? "|OLD|0":"|NEW|1")}' file2 file3 file1 > fin.txt

Output of fin.txt
Code:
20160302|5485368299953|96|510101223440252|USA|5485368299953|6|800|2300|0|NEW|1
20160530|5481379883742|7|510101242850814|USA|5481379883742|5|540|2181|1500|NEW|1
20160513|5481279653404|24|510100412142433|INDIA|5481279653404|3|380|1900|0|OLD|0
20160112|5481367419640|146|510101245923316|INDIA|5481367419640|1|60|1500|3500|NEW|1
20160329|5481274428798|69|510100421020557|CHINA|5481274428798|3|380|1900|0|OLD|0
20160603|5482279814886|3|510101249024025|CHINA|5482279814886|5|200|1700|330|NEW|1
20160112|5482282312263|146|510101268030942|UK|5482282312263|5|200|1700|330|NEW|1
20160502|5482279817754|35|510100400128133|UEA|5482279817754|3|380|1900|599|NEW|1
20160120|5482281112440|138|510100421021084|UEA|5482281112440|2|150|1650|0|NEW|1

Last edited by Aia; 06-12-2016 at 06:37 PM.. Reason: rename variables
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Compare data - Match first column and compare second

Hi guys, looking for some help with a way to compare data in two files but with some conditions. example, File 1 consists of site1,10.1.1.1 site2,20.2.2.2 site3,30.3.3.3 File 2 contains site1,l0.1.1.1 site2,50.1.1.1 site3,30.3.3.3 site4,40.1.1.1 I want to be able to match the... (1 Reply)
Discussion started by: mutley2202
1 Replies

2. Shell Programming and Scripting

Howto compare the columns of 2 diff tables of 2 different schemas in UNIX shell script

HI All, I am new to Unix shell scripts.. Could you please post the unix shell script for for the below request., There are two different tables(sample1, sample2) in different schemas(s_schema1, s_schema2). Unix shell script to compare the columns of two different tables of two... (2 Replies)
Discussion started by: Rajkumar Gopal
2 Replies

3. Shell Programming and Scripting

Require compare command to compare 4 files

I have four files, I need to compare these files together. As such i know "sdiff and comm" commands but these commands compare 2 files together. If I use sdiff command then i have to compare each file with other which will increase the codes. Please suggest if you know some commands whcih can... (6 Replies)
Discussion started by: nehashine
6 Replies

4. Shell Programming and Scripting

Compare the rowcount from tables of two databases

All, I have to create a shell script to comapre the rowcount of 120 tables from two oracle databases. In fact, using informatica the data is loading from 120 source tables to 120 staging tables. After that, they want to have a shell script to comapre the rowcount of all these tables. 1) I... (3 Replies)
Discussion started by: Maya_Pillai
3 Replies

5. Shell Programming and Scripting

Tables and borders

when i do this: cat HITS i get the following displayed: sport.hits:87.114.172.31 Thu Sep 28 22:45:12 GMT 2006 how do i put this information into a bordered table? so it will output like this: ...........File /... (9 Replies)
Discussion started by: amatuer_lee_3
9 Replies

6. Shell Programming and Scripting

tables in scripts

Hi , I have two tables with same length t1 and t2, I want to cretae a new third table where i put the difference between the elements of t2 and t1, t3= t1 - t2 t3= t1 - t2 I am new to scripts, any help please? thanks (7 Replies)
Discussion started by: Celine19
7 Replies

7. Shell Programming and Scripting

compare two tables using shell script

Hi, I want to compare two tables fieldwise using shell script. Can anyone help me regarding the same. The approach which i tried is to first move the two tables in simple txt file where each field is now seperated by space. But i can't retrive each field with "space" as a seperator b'coz there... (1 Reply)
Discussion started by: dtidke
1 Replies

8. Shell Programming and Scripting

Converting tables of row data into columns of tables

I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated. Input: test_data_1 1 2 90% 4 3 91% 5 4 90% 6 5 90% 9 6 90% test_data_2 3 5 92% 5 4 92% 7 3 93% 9 2 92% 1 1 92% ... Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies

9. UNIX for Dummies Questions & Answers

viewing tables

I have completely blanked out on this and I have done it a million times. I need to modify some tables in unix. What is the command for opening/viewing the tables? Thanks so much. :o (2 Replies)
Discussion started by: itldp
2 Replies
Login or Register to Ask a Question