Concatenate 2 different VB files to a new VB file

santhosh5983 · New User Joined: 11 Mar 2015 Posts: 10 Location: India

Hi,

I have a requirement to concatenate 2 files based on account number field. The position of the account number field in each of the input file is different. Please find the file 1 and file 2 format and the output that am expecting. Please let me know how can this be achieved thru sort ?

Input File 1 - Variable block say of length 50. Account number position is 15 of size 5.

Nic Clouston · Posted: Sat Oct 10, 2015 2:25 am

Will there always be one record for each account on each file. Are the files in account number sequence?

santhosh5983 · New User Joined: 11 Mar 2015 Posts: 10 Location: India

Yes the files will be sorted on account number. And yes both the files will have one record for every account number.

As part of error handling, it will be great if we can push the records that does not have the corresponding matching record to a different/separate file.

Rohit Umarjikar · Posted: Sat Oct 10, 2015 2:40 am

I see the file is already sorted by account number so all you need to do is add sequence number to each file and then sort based on that.

Rohit Umarjikar · Posted: Sat Oct 10, 2015 3:13 am

I got disconnected so couldn't edit my post.
So to continue, may be use a join key on account number or the sequence number as told above and reformat them together and then resize based on record length, Not sure if that works but try.

Bill Woodger · Posted: Sat Oct 10, 2015 4:10 am

JOINKEYS on the account number.

Variable-length REFORMAT record, although one of the records (better the short one) need to be padded to fixed-length in the REFORMAT. Also specify the match-marker. JOIN UNMATCHED,F1,F2. There OUTFILs, one for matched, one for unmatched F1, one for unmatched F2.

On your match, BUILD, using the slash operator (/) to create two records from one OUTFIL input. Use VLTRIM to make the records variable again, being careful of what values may be in the final field on each record (you don't want to lose necessary trailing blanks).

Rohit Umarjikar · Posted: Mon Oct 12, 2015 8:36 pm

Bill, Can we use resize(ICETOOL) as below as a alternative to '/'? any downsides?
e.g.Vb is not considered for this example.

Bill Woodger · Posted: Mon Oct 12, 2015 9:41 pm

RESIZE is for fixed-length records only.

With the COPY already being a JOINKEYS, the RESIZE would add a second pass of the data.

You could make a fixed-length REFORMAT record, and then RESIZE, but you still have the extra pass.

ICETOOL's COPY and SORT can use JKFROM. RESIZE cannot. Can't avoid the two passes.

Rohit Umarjikar · Posted: Tue Oct 13, 2015 3:00 am

Thanks Bill.