Combine matching records from two different input, and forma

jz1b0c · Posted: Fri Apr 20, 2007 2:13 am

Frank,

This is almost similar to the question you have already answered, but with minimal requirement changes.

Input1:
Column : Col1 Col2 Col3 Col4 Col5
Position : 1-5 6-10 11-15 16-26 27-40

Input2:
Column : Col1 Col2 Col6 Col7 Col8
Position : 1-5 6-10 11-15 16-26 27-40

For all matching Col1, Col2 in both the inputs
Output: Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8

If Input1 has the key but not Input2 then it should be reported to another file as
Col1 Col2 Col3 Col4 Col5 blank blank blank

Here is the example:

iNP1:
AAA 1111 A1A1A1 A2A2A2 A3A3A3
AAA 2222 A1A1A1 A2A2A2 A3A3A3
BBB 1111 B1B1B1 B1B1B1 B1B1B1

INP2:

AAA 1111 X1X1X1 X2X2X2 X3X3X3
AAA 2111 AXAXAX AXAXAX AXAXAX
BBB 1111 BXBXBX BXBXBX BXBXBX

OUTPUT:

AAA 1111 A1A1A1 A2A2A2 A3A3A3 X1X1X1 X2X2X2 X3X3X3
AAA 2222 A1A1A1 A2A2A2 A3A3A3 N/A N/A N/A
BBB 1111 B1B1B1 B1B1B1 B1B1B1 BXBXBX BXBXBX BXBXBX

INSTEAD OF N/A you can write any character say 'XXXX' etc.

jz1b0c · Posted: Fri Apr 20, 2007 2:15 am

My mistake, instead of another file, it should be written to same file..
The example is correct.

Frank Yaeger · Posted: Fri Apr 20, 2007 4:27 am

What do you want to do with a record in input2 that doesn't have a match in input1?

Can there be duplicates in input1? If so, show an example.

Can there be duplicates in input2? If so, show an example.

jz1b0c · Posted: Fri Apr 20, 2007 7:50 pm

Frank,

Col1 + col2 is the key, this combination is unique.

There wont be any duplicate keys in any of the input

Input1 is the file of our concern,
For every record of Input1 check for a match in input2.
(If Input2 has a record, and Input1 doesn't then ignore it)

iNP1:

Frank Yaeger · Posted: Fri Apr 20, 2007 10:07 pm

Here's a DFSORT/ICETOOL job that will do what you asked for. I'm not sure if I have the column positions right but this should give you the idea and you can adjust the positions as needed.