Merging two special character separated files based on pattern matching

Shell Programming and Scripting
1

Hi.

I have 2 files of below format.

File1

AA~1~STEVE~3.1~4.1~5.1
AA~2~DANIEL~3.2~4.2~5.2
BB~3~STEVE~3.3~4.3~5.3
BB~4~TIM~3.4~4.4~5.4

File 2

AA~STEVE~AA STEVE WORKS at AUTO COMPANY
AA~DANIEL~AA DANIEL IS A ELECTRICIAN
BB~STEVE~BB STEVE IS A COOK

I want to match 1st and 3rd column values of file 1 against 1st and 2nd column values of file2. If both match conditions satisfy then i want to print 3rd column of file2 at the end of file1.

If not then print "UNDEFINED" at end of file 1

Below is the expected output

AA~1~STEVE~3.1~4.1~5.1~AA STEVE WORKS at AUTO COMPANY
AA~2~DANIEL~3.2~4.2~5.2~AA DANIEL IS A ELECTRICIAN
BB~3~STEVE~3.3~4.3~5.3~BB STEVE IS A COOK
BB~4~TIM~3.4~4.4~5.4~UNDEFINED JOB

Please suggest how i can make this work. I have tried using many combinations with awk but its not working.

2

An awk approach:

awk -F'~' '
        NR == FNR {
                A[$1] = $1
                B[$2] = $2
                C[$1,$2] = $0
                next
        }
        {
                n = split ( C[$1,$3], V, "~" )
                if ( A[$1] && B[$3] )
                        print $0, V[n]
                else
                        print $0,"UNDEFINED JOB"
        }

' OFS="~" file2 file1
3

Try:

awk 'NR==FNR{A[$1,$2]=$3; next} {print $0, ($1,$3)in A ? A[$1,$3] : "UNDEFINED JOB"}' FS=\~ OFS=\~ file2 file1