Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Join

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Join
# 8  
Old 06-04-2009
nawk -f green.awk file2 file1

green.awk:
Code:
BEGIN {
  FS=OFS="|"
}
FNR == NR {
    if ($2 =="P" && !($1 in phone))
       phone[$1]=$3
    if ($2 =="F" && !($1 in fax))
       fax[$1]=$3
    next
}
{
   printf("%s%s%c%s%s\n", $0, ($1 in phone)? phone[$1]:"", OFS,($1 in fax)? fax[$1]:"", OFS)
}

# 9  
Old 06-04-2009
vgersh99,

It works...my apologies for not putting the data right.. a small change please...

file1:
----
1|abc|def|ghi|
2|aaa|bbb|ccc|
file2:
-----
1|abc|P|1111111111|
1|abc|P|2222222222|
1|bbc|F|3333333333|
2|aaa|P|7777777777|

after join I should get
1|abc|def|ghi|1111111111||
2|aaa|bbb|ccc|7777777777||

file 1 and file should be matched on first and second fields instead of just matching on the first one...Thanks for your help
# 10  
Old 06-04-2009
Code:
BEGIN {
  FS=OFS="|"
}
{idx=$1 SUBSEP $2}
FNR == NR {
    if ($3 =="P" && !(idx in phone))
       phone[idx]=$4
    if ($3 =="F" && !(idx in fax))
       fax[idx]=$4
    next
}
{
   printf("%s%s%c%s%s\n", $0, (idx in phone)? phone[idx]:"", OFS,(idx in fax)? fax[idx]:"", OFS)
}

# 11  
Old 06-05-2009
one last try please...not sure what i messed up...here is the real data I am trying to merge

file1:
----
1|4535|78456|abc|def|ghi|
2|4673|73766|aaa|fgh|dfg|
3|7834|94583|bbb|tyr|yut|
file2:
-----
1|abc|P|1111111111|
1|abc|P|2222222222|
1|bbc|F|3333333333|
2|aaa|P|7777777777|
3|bbb|P|1234567890|
3|bbb|F|9876543211|

to be joined on comparing 2 fields (1 &4 in first file / 1 & 2 in second file)

after join I should get
1|4535|78456|abc|def|ghi|1111111111||
2|4673|73766|aaa|fgh|dfg|7777777777||
3|7834|94583|bbb|tyr|yut|1234567890|9876543211|


with previous code I'm getting

1|4535|78456|abc|def|ghi|||
2|4673|73766|aaa|bbb|ccc|||
3|7834|94583|bbb|tyr|yut|||

any help is appreciated...Thanks
# 12  
Old 06-05-2009
To be honest with you...... by now you should be able to adjust the posted solution ANY WAY you'd like to fit your changing requirements - this is REALLY easy given you understand what's being done (which you should - otherwise there's no point going through this exercise endlessly).
Code:
BEGIN {
  FS=OFS="|"
}
FNR == NR {
    idx=$1 SUBSEP $2
    if ($3 =="P" && !(idx in phone))
       phone[idx]=$4
    if ($3 =="F" && !(idx in fax))
       fax[idx]=$4
    next
}
{
   idx=$1 SUBSEP $4
   printf("%s%s%c%s%s\n", $0, (idx in phone)? phone[idx]:"", OFS,(idx in fax)? fax[idx]:"", OFS)
}

Last edited by vgersh99; 06-05-2009 at 10:30 AM..
# 13  
Old 06-05-2009
vgersh99,

It works perfectly...I really appreciate your help...I'm not a big UNIX guy...

Thank you so much...
# 14  
Old 07-21-2009
join issue
I have 2 files

1.txt
-----
Code:
13
14
16
3



2.txt
-----
Code:
13|BV1876032
13|BV9252040
14|MR0316097
17|MH0839742



The following

Code:
nawk '
BEGIN {
FS=OFS="|"
}
NR==FNR {
a[$1]=$2 $3 ; next
}
{
print c = ($1 in a) ? $0 FS a[$1] : $0 OFS
}
' 2.txt 1.txt

gives

Code:
13|BV9252040
14|MR0316097
16|
3|



Missing the second entry in 2.txt file...output should be

Code:
13|BV9252040
13|BV1876032
14|MR0316097
16|
3|



any help is appreciated

Thanks
Last edited by vgersh99; 07-21-2009 at 04:23 PM.. Reason: code tags, PLEASE!
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies

2. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

3. UNIX for Dummies Questions & Answers

how to join two files using "Join" command with one common field in this problem?

file1: Toronto:12439755:1076359:July 1, 1867:6 Quebec City:7560592:1542056:July 1, 1867:5 Halifax:938134:55284:July 1, 1867:4 Fredericton:751400:72908:July 1, 1867:3 Winnipeg:1170300:647797:July 15, 1870:7 Victoria:4168123:944735:July 20, 1871:10 Charlottetown:137900:5660:July 1, 1873:2... (2 Replies)
Discussion started by: mindfreak
2 Replies

4. Shell Programming and Scripting

Join help

im trying to join 3 files together sort -n ${ASS1_DATA_DIR}Prac1 | sed '/^#/d' > ${ASS1_OUTPUT_DIR}Prac1temp sort -n ${ASS1_DATA_DIR}Prac2 | sed '/^#/d' > ${ASS1_OUTPUT_DIR}Prac2temp join -a1 -a2 -1 1 -2 1 -o "0 1.2 2.2" -e " " ${ASS1_OUTPUT_DIR}Prac1temp ${ASS1_OUTPUT_DIR}Prac2temp >... (6 Replies)
Discussion started by: bigubosu
6 Replies

5. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7... (3 Replies)
Discussion started by: InfoSeeker
3 Replies

6. UNIX for Dummies Questions & Answers

Use of join

I want basically to merge two config files each of them look like that File:Option1 value1 Optionx valuex .... One of those files is default config while the other is generated by my script. Now here is the problem, when my script generates an option that previously has been set by default i... (4 Replies)
Discussion started by: Adas
4 Replies

7. Programming

sql,multiple join,outer join issue

example sql: select a.a1,b.b1,c.c1,d.d1,e.e1 from a left outer join b on a.x=b.x left outer join c on b.y=c.y left outer join d on d.z=a.z inner join a.t=e.t I know how single outer or inner join works in sql. But I don't really understand when there are multiple of them. can... (0 Replies)
Discussion started by: robbiezr
0 Replies

8. Shell Programming and Scripting

Join

Hi, need some help in joining please, FILE 1: ------- 1|05/20/2009| 2|04/21/2009| 2|03/21/2009| FILE 2: ------- 1|Michel|Hawkins|05/10/2009| 1|Michel|Hawkins|03/10/2007| 2|Krish|Lander|09/10/2005| FILE 3: ------- 1|M|32|03/22/2009| 2|M|42|04/22/2009| 2|M|41|03/10/2008| I want to... (7 Replies)
Discussion started by: greenworld
7 Replies

9. Shell Programming and Scripting

join (pls help on join command)

Hi, I am a new learner of join command. Some result really make me confused. Please kindly help me. input: file1: LEO oracle engineer 210375 P.Jones Office Runner ID897 L.Clip Personl Chief ID982 S.Round UNIX admin ID6 file2: Dept2C ID897 6 years Dept5Z ID982 1 year Dept3S ID6 2... (1 Reply)
Discussion started by: summer_cherry
1 Replies
Login or Register to Ask a Question