Have three files. Any other approach with regards to file concatenation or splitting, etc is appreciated
If column55(billngtype) of file1 contains YMNC or YPBC then pick the value of column13(documentnumber). Now find this documentnumber in column1(Billdoc) of file2 and grep the corresponding value of column6(price) in File2. Now need to find this value taken from column6(price) of File2 in column1(salesdoc) of File3 and need to fetch the corresponding value from column2(Refdoc) against the value from Column1(salesdoc) of FIle3. If value is captured from column2(refdoc) of File3. Put that value in column15(originadocumentnumber) of File1.
For ex-
if File 1,column55(billingtype) has YMNC and column13(documentnumber) values is 420075416 and same is present in column1(Billdoc) of File2 in last row, then fetch the value from column6(price) which is 5049641151 against that column1(billdoc) 420075416. Now find the value taken from column6(price) of FIle2 in FIle3. We can see 5049641151 is present in column1(Salesdoc) in File3. Now fetch the corresponding value from column2(Refdoc) which is 6225972627. Now copy the final value(6225972627) fetched in column15(originaldocumentnumber) against the column13(documentnumber) 420075416
File1
In actual, column billingtype is column55 and column documentnumber is column 13 and column originaldocumentnumber is column 15.
Below is sample only
billingtype documentnumber originaldocumentnumber
YMNC 420075416 765467
YMNC 429842808
YPBC 429842809
INV 430071605 7688888
YPBC 430071609
File2
In actual, column billdoc is column1 only and column price is column 6
Billdoc price
4200754167 5049641141
429842808 5049641143
6400392213 5049641145
430071609 5049641147
429842809 5049641149
420075416 5049641151
File3
Below sample is as it is
Salesdoc Refdoc
5049641151 6225972627
5049641143 6225973664
5049641147 6225973574
5049641145 6225973553
5049641149 6225973639
5049641141 6225973652
Expected Output File1
billingtype documentnumber originaldocumentnumber
YMNC 420075416 6225972627
YMNC 429842808 6225973664
YPBC 429842809 6225973639
INV 430071605 7688888
YPBC 430071609 6225973574
Code tried so far
awk -F"|" -v OFS="|" 'FILENAME=="File2"{bd[$1]=$6;next}
FILENAME=="File3"{sd[$1]=$2;next}
FNR>1 && ($55~/YMNC|YPBC) {$15=sd[bd[$13]]}1' File2 File3 File1
Above code is returning source file as it is and is not putting values in column originaldocumentnumber(column 15) in source file(File1)