Shell Programming and Scripting

Hi,

I have two input files as

Code:

File1 :

ABC:client1:project1
XYZ:client2-aa:project2
DEF:client4:proj

Code:

File2 :

client1:W-170:xx
client2-aa:WT-04:yy
client4:L-005A:zz

Also, array of valid values can be hardcoded like [W,WT]

Code:

Output :

ABC:W:project1
XYZ:WT:project2

SO basically, if the column2 of file1 matches any record's column1 in file2, then replace that column2 in file1 by a part of column2 from file2.
TO obtain this part I need to split column2 in file2 using "-" operator and match the results against an array to determine valid values.

For the records not matching above criteria, they should NOT be displayed in the output.

I am new to shell scripting so would appreciate any help.
I have been trying since a couple of days and have been able to replace the entire column using

Code:

awk -F":" 'NR==FNR{A[$1]=$2;next}$2 in A{$2=A[$2]}1' OFS=":" file2 file1

However, haven't been able to get the part of the column requirement working.

How about this:

Code:

awk -F: -vLST=W,WT 'BEGIN{split(LST,s,",");for(k in s) OK[s[k]]=k;}
  NR==FNR{sub(/-.*/,"",$2);if($2 in OK)A[$1]=$2;next}
  $2 in A&&$2=A[$2]' OFS=":" file2 file1