Shell Programming and Scripting

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

Code:

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

Code:

 Billdoc   price
4200754167  5049641141
429842808   5049641143
6400392213  5049641145
430071609   5049641147
429842809   5049641149
420075416   5049641151

File3
Below sample is as it is

Code:

  Salesdoc  Refdoc  
5049641151  6225972627
5049641143  6225973664
5049641147  6225973574
5049641145  6225973553
5049641149  6225973639
5049641141  6225973652

Expected Output File1

Code:

billingtype documentnumber  originaldocumentnumber
YMNC            420075416    6225972627
YMNC            429842808    6225973664
YPBC            429842809    6225973639
INV             430071605     7688888
YPBC            430071609    6225973574

Code tried so far

Code:

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)

Code:

FNR>1 && ($55~/(YMNC|YPBC)/) {$15=sd[bd[$13]]}

Hi rdrtx1,

Sorry but this won't work.
as statement

Code:

(FNR>1 && ($55~/(YMNC|YPBC)/) {$15=sd[bd[$13]]})

got printed without "/" by mistake.

Adapting your code to your samples given I find it doing exactly what you want:

Code:

awk  '
FILENAME=="file2"       {bd[$1]=$2; next}
FILENAME=="file3"       {sd[$1]=$2; next}
FNR>1 && 
($1 ~ /Y[MP][NB]C/)     {$3 = sd[bd[$2]]}
1
' file2 file3 file1
billingtype documentnumber  originaldocumentnumber
YMNC 420075416 6225972627
YMNC 429842808 6225973664
YPBC 429842809 6225973639
INV              430071605    7688888
YPBC 430071609 6225973574

Could you try that as well and post exactly how and where it fails?

Hi Rudic,

It worked now..actually one file containing extra character..have checked with cat -v and found it..