[SOLVED] Unix : lookup value from one file to other files based on two joining keys

Issue

I'm looking for help on joining(at unix level) 2 files(file1 n file2),
pickup values from file2 as a priority on file1. i.e if value in tmpValue exists on file2 that should be taken instead of file1 srcValue, if no tmpValue on file2 then use srcValue on file1 tmpValue.
Note : lookup has to be done based on 2 key columns : id and dept sample

file1
------
id^dept^name^srcValue
1^d1^a^s123    
2^d2^b^s456    
3^d3^c^        

file2
--------

id^dept^name
1^d1^Tva
3^d3^TVb
4^d4^Tvm  

Desired output
---------------------
id^dept^name^FinalValue 
1^d1^a^Tva          
2^d2^b^s456         
3^d3^c^TVb      

Below sample code works fine if i consider only 1 column as key column(id), but I'm not sure how to mention both id and dept as key columns.

  awk -F"^" 'BEGIN{OFS="^"} 
               { 
            if (NR==FNR) { 
                a[$1]=$3;     
                next  
            }   
            if ($1 in a){
                $4=a[$1] 
            }
             print
           }' file2 file1 


output of same(above)code
id^dept^name^name
1^d1^a^Tva
2^d2^b^s456
3^d3^c^TVb

Solution

I'm not sure how to mention both id and dept as key columns.

Store both.

a[$1, $2]=$3;

And compare both.

if (($1, $2) in a) {

Comma just "merges" values using OFS. So $1, $2 is basically equal to $1 OFS $2 - it's a string with ^ in between. You can print ($1, $2) for example to inspect it.

Answered By - KamilCuk