I have 3 file inputs,
file1
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
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
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
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?
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