SPLICE problem when trying to join 2 files

julienloc · New User Joined: 17 Aug 2007 Posts: 29 Location: Brussels

Hi all,

I'm trying to join 2 files, but I cannot manage to have the desired result.
Here is what I try to do:

----key----
IN1:

AAAA 4444
BBBB 2222
BBBB 1111
KKKK 3333
AAAA 7777
HHHH 7777
HHHH 1111
EEEE 1111

-key-
IN2:

AAAA DATA1
EEEE DATA2
HHHH DATA3
YYYY DATA4

OUTPUT:

AAAA 4444 DATA1
BBBB 2222
BBBB 1111
KKKK 3333
AAAA 7777 DATA1
HHHH 7777 DATA3
HHHH 1111 DATA3
EEEE 1111 DATA2

In IN1, each record is unique (the whole record is unique key).
In IN2, we have as key a subkey of IN1 key format (4 first chars)

Requirement:
OUTPUT contains all IN1 records, but if an IN1 record has its subkey match the key of IN2, then the DATA field of the matching IN2 record has to be appended to the IN1 OUTPUT record.

I've tried with SPLICE, but it seems no option is adequate (WITHALL, KEEPNODUPS, KEEPBASE) nor copying the files to the temp file in one order or another.
The problem is that key2 is a subkey of key1. If key1 and key2 where the same, then I could do:
1) append IN2 to IN1. SPLICE on key WITHALL.

If I do this with our input files and SPLICING on key2, then records would be lost (aggregation of same subkey in IN1)

Any idea?

Thanks.

Julien

Frank Yaeger · Posted: Wed Oct 10, 2007 10:23 pm

Here's a DFSORT/ICETOOL job that will do what you asked for:

julienloc · New User Joined: 17 Aug 2007 Posts: 29 Location: Brussels

This works! This was kind of tricky with the 'BB' 'VV'.
I'm impressed with DFSORT again.
Thank you Frank.