Visit Our UNIX and Linux User Community


awk script to find data in three file and perform replace operation


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk script to find data in three file and perform replace operation
# 1  
Old 03-29-2018
awk script to find data in three file and perform replace operation

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)
# 2  
Old 03-29-2018
Code:
FNR>1 && ($55~/(YMNC|YPBC)/) {$15=sd[bd[$13]]}

# 3  
Old 03-29-2018
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.
# 4  
Old 03-30-2018
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?
# 5  
Old 04-03-2018
Hi Rudic,

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

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy last few lines of a file, perform math operation and iterate further

Hi, I am trying to generate a data of following order: 4 0 1 642 643 4 642 643 1283 1284 4 1283 1284 1924 1925 4 1924 1925 2565 2566 4 2565 2566 3206 3207 4 3206 3207 3847 3848 4 3847 3848 4488 4489 4 4488 4489 5129 5130 ---------------------- 4 1 2 643 644 4 643 644 1284... (6 Replies)
Discussion started by: SaPa
6 Replies

2. Shell Programming and Scripting

awk --> math-operation in data-record and joining with second file data

Hi! I have a pretty complex job - at least for me! i have two csv-files with meassurement-data: fileA ...... (2 Replies)
Discussion started by: IMPe
2 Replies

3. Shell Programming and Scripting

How To Perform Mathematical Operation Within If in awk?

Hi All, I am using an awk script as below: awk -F'|' 'BEGIN{OFS="|";} { if ($1==$3 && $3==$7 && $7==$13 && $2==$6 && $6==$11 && $15-$14+1==$11) print $0"|""TRUE"; else print $0"|""FALSE"; }' tempfile.txt In above script, all conditions are being checked except the one which is... (4 Replies)
Discussion started by: angshuman
4 Replies

4. Homework & Coursework Questions

Using dbms_pipe with C++ to perform daabase operation

I am getting two result: string and int in c++ code. That I want to store into database. The request which generates result is very frequent. So each time performing db operation to store the result is costly for me. So how this can be achived using dbms_sql? I dont have any experience and how... (1 Reply)
Discussion started by: karimkhan
1 Replies

5. Shell Programming and Scripting

sed command to skip the first line during find and replace operation

Hi Gurus, I did an exhaustive search for finding the script using "sed" to exclude the first line of file during find and replace. The first line in my file is the header names. Thanks for your help.. (4 Replies)
Discussion started by: ks_reddy
4 Replies

6. Shell Programming and Scripting

[Solved] Perform an operation to all directories

Sorry, about this thread - I solved my own problem! Thanks for taking a look. edit by bakunin: no problem, but it would have been a nice touch to actually tell us what the solution was. This would have been slightlich more educating than just knowing that you found it. I changed your title to... (0 Replies)
Discussion started by: Blue Solo
0 Replies

7. Shell Programming and Scripting

awk based script to find the average of all the columns in a data file

Hi All, I need the modification for the below mentioned code (found in one more post https://www.unix.com/shell-programming-scripting/27161-script-generate-average-values.html) to find the average values for all the columns(but for a specific rows) and print the averages side by side. I have... (4 Replies)
Discussion started by: ks_reddy
4 Replies

8. Shell Programming and Scripting

Awk based script to find the median of all individual columns in a data file

Hi All, I have some data like below. Step1,Param1,Param2,Param3 1,2,3,4 2,3,4,5 2,4,5,6 3,0,1,2 3,0,0,0 3,2,1,3 ........ so on Where I need to find the median(arithmetic) of each column from Param1...to..Param3 for each set of Step1 values. (Sort each specific column, if the... (5 Replies)
Discussion started by: ks_reddy
5 Replies

9. Shell Programming and Scripting

Find and replace data in text file with data in same file

OK I will do my best to explain what I need help with. I am trying to format an ldif file so I can import it into Oracle oid. I need the file to look like this example. Keep in mind there are 3000 of these in the file. changetype: modify replace: userpassword dn:... (0 Replies)
Discussion started by: timothyha22
0 Replies

10. Shell Programming and Scripting

How to perform arithmetic operation on date

Hi all, I would appreciate if anyone knows how to perform adding to date. As for normal date, i can easily plus with any number. But when it comes to month end say for example 28 Jun, i need to perform a plus with number 3, it will not return 1 Jul. Thanks in advance for your help. (4 Replies)
Discussion started by: agathaeleanor
4 Replies

Featured Tech Videos