Merging matching records from two files



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 8:59 pm

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.
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Re: Merging matching records from two files

 

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 9:00 pm

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.
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 9:04 pm

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 :)
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 9:05 pm

Still combining into 1 left
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby enrico-sorichetti » Mon Jan 02, 2012 9:34 pm

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...
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2643
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Merging matching records from two files

Postby BillyBoyo » Mon Jan 02, 2012 9:39 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 10:59 pm

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.
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 11:00 pm

Hi Enrico,

I have made changes to the both, so no worry at that part.
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby BillyBoyo » Mon Jan 02, 2012 11:11 pm

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))
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 11:14 pm

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 :)
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

PreviousNext

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post