How To Look-Up (Part 3) in File Data Using AWK

Main Input File: F_Data_File1.txt

EMPID|ENAME|DEPT|FLAG
10001|A1|TRANS|Y
10002|A2|MED|Y
10003|A3|FIN|N
10004|A4|HR|Y
10005|A5|CSR|N
10006|A6|TRANS|Y
10007|A7|FIN|Y
10008|A8|HR|N

Look-Up File : F_Data_File2.txt

Y|ACTIVE
N|INACTIVE

Desired Output: We want to update the Value associated with FLAG Y & N with Active or Inactive accordingly

10001|A1|TRANS|ACTIVE

10002|A2|MED|ACTIVE

10003|A3|FIN|INACTIVE

10004|A4|HR|ACTIVE

10005|A5|CSR|INACTIVE

10006|A6|TRANS|ACTIVE

10007|A7|FIN|ACTIVE

10008|A8|HR|INACTIVE

Solution:

$ awk 'BEGIN {FS=OFS="|"}FNR==NR{V_LkUp[$1]=$2;next}($4 in V_LkUp)  {print $1,$2,$3,V_LkUp[$4]}' F_Data_File2.txt F_Data_File1.txt

$ awk 'BEGIN {FS=OFS="|"}FNR==NR{V_LkUp[$1]=$2;next}($4 in V_LkUp)  {print $1,$2,$3,V_LkUp[$4]"["$4"]" }'  F_Data_File2.txt F_Data_File1.txt

Will produced below Output:

10001|A1|TRANS|ACTIVE[Y]

10002|A2|MED|ACTIVE[Y]

10003|A3|FIN|INACTIVE[N]

10004|A4|HR|ACTIVE[Y]

10005|A5|CSR|INACTIVE[N]

10006|A6|TRANS|ACTIVE[Y]

10007|A7|FIN|ACTIVE[Y]

10008|A8|HR|INACTIVE[N]

Explanation:

1. Array V_LkUp holds the LookUp table data. In loop $4, 4th field of Main file is lookuped     in array V_LkUp. Whenever Y is found it returns ACTIVE or else INACTIVE.

2. If you have not checked other post on Look-Up please go through 

• How To Look-Up in File Data Using AWK
• How To Look-Up (Part 2) in File Data Using AWK