Multiple File with Lookup - Help

[taylor5pak]

Hi all,

I am hoping someone can help me... I have to compare two files and where I have a match on file 1 field x and file 2 field x, I need to pull a value from file 1 (field w)..

Here is a sample:

for example purposes
awk '
FILENAME = ARGV[1] {x[substr($0,23,18) = substr($0,62,18)}
FILENAME = ARGV[2] {s = substr($0,62,18)
if (s in x)
....
}' file1 file2 > output

here is where I am stuck.. I have a match, now I need to retrieve a field in file 1 ($0,5,12) where these are equal and replace the same field in the input file with the new value from file 1... I have the output down it is only retrieving the value where I am stuck...

Thank you very much!
Donna

 

[PHV]

Have you tried something like this ?
awk '
{s=substr($0,62,18)}
FILENAME==ARGV[1]{x=substr($0,5,12)}
FILENAME==ARGV[2]{
if(x>"")$0=substr($0,1,4)xsubstr($0,17)
print
}' file1 file2 > output

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[taylor5pak]

Hello,

I did try this and it does it on every line...

What I have is this.. I have a huge file coming in where the item number is at byte (62,18) and I need to cross reference/match up this field with a field from another file where the field is at (23,18) and pull out bytes (5,18) from this second file where we looked for the match ...

This huge file has many record types and the only ones we are concerned about are item records and we just want to print the rest..

The criteria goes somewhat like this after the prior recommendation...

awk '
{recno = substr($0,19,3)
s = substr($0,62,18)}
FILENAME == ARGV[1] {x = substr($0,5,18)}
FILENAME == ARGV[2] {
if (recno ~ /(700|710|720|790|800|810|820|890)/)
{ if(x>"")$0=substr($0,1,4)xsubstr($0,17)
print
} else print $0
}' pcua.unl invoice_file > test.out

Thanks again for any help
Donna

 

[PHV]

Something like this ?
awk '
FILENAME==ARGV[1]{x[substr($0,23,18)]=substr($0,5,18)}
FILENAME==ARGV[2]{
if(substr($0,19,3)~/(70|71|72|79|80|81|82|89)0/)
if(x[substr($0,62,18)]>"")
$0=substr($0,1,4)xsubstr($0,23)
print
}' pcua.unl invoice_file > test.out

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[taylor5pak]

Hello,

First off thank you very much for responding... I have tried this and again not good.. I reran your first suggestion thru with a slight modification and have been able to replace the item field,

awk '
{s=substr($0,62,18)}
FILENAME==ARGV[1]{x[substr($0,24,18)]=substr($0,5,18)}
FILENAME==ARGV[2]{
if(substr($0,19,3)~/(70|71|72|79|80|81|82|89)0/)
if(x>"")$0=substr($0,1,61)xsubstr($0,18)
} ' input1 input2 > output

Where input2 is really my looping file and in the above the output gives me the first 1,61 from input2, then jumps and gets the x which is the field from input1 and resumes to output from input2 19,on... I need it to actutally go input2 1,61 , input1 x , input2 the remainder of the file...

Thanks soooo sooo much for the help on this...
Donna

 

[taylor5pak]

Hi, sorry when I say at the end the remainder of the file, I mean from byte 81, on the rest of input2... It should be looping thru input2 replacing the itm number where necessary and outputting the exact input2 file with the new itm number where required... I hope this helps..

Donna

 

[PHV]

If the rules aren't changed one more time, you may try something like this:
awk '
FILENAME==ARGV[1]{x[substr($0,24,18)]=substr($0,5,18)}
FILENAME==ARGV[2]{
if(substr($0,19,3)~/(70|71|72|79|80|81|82|89)0/){
s=substr($0,62,18)
if(x>"")$0=substr($0,1,61)xsubstr($0,80)
};print
}' input1 input2 > output

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[taylor5pak]

Hello PH

That worked ..... YOU ARE THE MAN !!! CYBER DRINK ON ME !!!!! Thank you soo much ....

Donna