Page 3 of 4

Re: Merging matching records from two files

PostPosted: Mon Jan 02, 2012 8:59 pm
by zhinghur
Hi Enrico,

There is little problem with the your code I used. I just found now.

Data is copying correct but for the first record of each key, it is indenting more value(original layout from byte 1) from file2 from left, as soon as I replace BUILD=(1,80)) to BUILD=(14,66))

Eg

./ ADD NAME=AB123
AB000000 07 0000001 123
AB000000 07 0000002 123
AB000000 07 0000003 123



output as

./ ADD NAME=AB123
AB000000 07 0000001 123
000002 123
000003 123
000002 123
000003 123


Please see if you can fix this.

Re: Merging matching records from two files

PostPosted: Mon Jan 02, 2012 9:00 pm
by zhinghur
enrico-sorichetti wrote:quoting the question that produced the snippet I posted
keep following on...
wouldn' t it be faster to get rid of file1
and build from scratch a ./ ADD NAME=XXYYY whenever the <key changes> on file 2
?


Ok, Thanks Enrico.

Re: Merging matching records from two files

PostPosted: Mon Jan 02, 2012 9:04 pm
by zhinghur
zhinghur wrote:Hi Enrico,

There is little problem with the your code I used. I just found now.

Data is copying correct but for the first record of each key, it is indenting more value(original layout from byte 1) from file2 from left, as soon as I replace BUILD=(1,80)) to BUILD=(14,66))

Eg

./ ADD NAME=AB123
AB000000 07 0000001 123
AB000000 07 0000002 123
AB000000 07 0000003 123



output as

./ ADD NAME=AB123
AB000000 07 0000001 123
000002 123
000003 123
000002 123
000003 123


Please see if you can fix this.




I was able to fixed it. Not necessary. Thank you anyway for the motivation :)

Re: Merging matching records from two files

PostPosted: Mon Jan 02, 2012 9:05 pm
by zhinghur
Still combining into 1 left

Re: Merging matching records from two files

PostPosted: Mon Jan 02, 2012 9:34 pm
by enrico-sorichetti
just looking at the snippet,
BUILD=(C'./ ADD NAME=',81,5,/1,80)),
IFTHEN=(WHEN=NONE,
BUILD=(1,80))


You changed only the second build,
You should change both 1,80 with the proper start,lenght

there is also a typo should have been /,1,80

I just checked, and all the Frank snippets use the /,1,80 format
but it works both ways, I wonder if there is glitch in the parsing...

Re: Merging matching records from two files

PostPosted: Mon Jan 02, 2012 9:39 pm
by BillyBoyo
The problem you are going to have with getting it in one step, I think, is that with the JOIN you are sorting into order but you said you want your output in the original order.

The two JOINKEYS are, by default, sorting the data on the keys for the join. You are doing a sort in the second step (I don't think it is necessary). You then need to get back to the original order, requiring the appending of a sequence number in the join processing and a sort on that sequence number and stripping the sequence number off.

I don't have access to the joinkeys functions, so can't test this, so you'll have to try.

Add SORTED,NOSEQCHK to both the JOINKEY statements and see if it does the correct selection. If that works, you're nearly there.

Re: Merging matching records from two files

PostPosted: Mon Jan 02, 2012 10:59 pm
by zhinghur
Its better we can sort. Join works fine. No problems with sorting for me.

But still I couldn't managed to make it one. I tried hard.

Hope anyone can combine now for me or can give any other solution I don't mind but in 1 step.

Re: Merging matching records from two files

PostPosted: Mon Jan 02, 2012 11:00 pm
by zhinghur
Hi Enrico,

I have made changes to the both, so no worry at that part.

Re: Merging matching records from two files

PostPosted: Mon Jan 02, 2012 11:11 pm
by BillyBoyo
If you no longer have a concern with the order of the output...

  JOINKEYS FILE=F1,FIELDS=(13,2,A,15,3,A)
  JOINKEYS FILE=F2,FIELDS=(1,2,A,21,3,A)
  REFORMAT FIELDS=(F2:1,80,F2:1,2,F2:21,3)
  OUTREC OVERLAY=(86:SEQNUM,5,ZD,RESTART=(81,5))
  OUTFIL IFTHEN=(WHEN=(86,5,ZD,EQ,1),
                      BUILD=(C'./ ADD NAME=',81,5,/1,80)),
  IFTHEN=(WHEN=NONE,BUILD=(1,80))

Re: Merging matching records from two files

PostPosted: Mon Jan 02, 2012 11:14 pm
by zhinghur
BillyBoyo wrote:If you no longer have a concern with the order of the output...

  JOINKEYS FILE=F1,FIELDS=(13,2,A,15,3,A)
  JOINKEYS FILE=F2,FIELDS=(1,2,A,21,3,A)
  REFORMAT FIELDS=(F2:1,80,F2:1,2,F2:21,3)
  OUTREC OVERLAY=(86:SEQNUM,5,ZD,RESTART=(81,5))
  OUTFIL IFTHEN=(WHEN=(86,5,ZD,EQ,1),
                      BUILD=(C'./ ADD NAME=',81,5,/1,80)),
  IFTHEN=(WHEN=NONE,BUILD=(1,80))



I will try this tomm. and get back to you. Looks like it should work. Thank you :)