JOINKEYS to find non matched records



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

JOINKEYS to find non matched records

Postby Papya013 » Wed Mar 30, 2016 10:20 am

Hi Team,

I have two input files as mentioned below. On non matching condition the input file FLAG which is at position 10 has to be overwritten with 'J'.

FILE1:

1234 ABCD N 9999


FILE2:

2345 ABCD J 9999
2356 ABCD N 9999


I tried with below mentioned SORT card but its not giving the results as expected instead I am getting the below mentioned output.
SORT CARD:
JOINKEYS FILES=F1,FIELDS=(12,4,A,5,4,A,10,1,A)          
JOINKEYS FILES=F2,FIELDS=(12,4,A,5,4,A,10,1,A)          
JOIN UNPAIRED,F1,ONLY                                    
REFORMAT FIELDS=(F1:1,16)                              
SORT FIELDS=(1,4,CH,A,5,4,CH,A)                          
OUTREC IFTHEN=(WHEN=(10,1,CH,EQ,C'N'),OVERLAY=(10:C'J'))


Current output:
1234 ABCD N 9999


Expected output:
1234 ABCD J 9999


Could anyone help me out with this ?
Papya013
 
Posts: 22
Joined: Wed Nov 06, 2013 6:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: JOINKEYS to find non matched records

 

Re: JOINKEYS to find non matched records

Postby Aki88 » Wed Mar 30, 2016 11:13 am

Hello,

Couple of minor changes to your SORT card; with your current SYSIN statement, no SORTOUT is generated since there are no unpaired records for the positions you've mentioned against your input data.
With updated offset and addition of first 4 bytes in the JOINKEYS statement in SYSIN:


 JOINKEYS FILES=F1,FIELDS=(13,4,A,6,4,A,11,1,A,1,4,A)    
 JOINKEYS FILES=F2,FIELDS=(13,4,A,6,4,A,11,1,A,1,4,A)    
 JOIN UNPAIRED,F1,ONLY                                  
 REFORMAT FIELDS=(F1:1,16)                              
 SORT FIELDS=(1,4,CH,A,6,4,CH,A)                        
 OUTREC IFTHEN=(WHEN=(11,1,CH,EQ,C'N'),OVERLAY=(11:C'J'))
 


It gives:

1234 ABCD J 9999


Hth.
Aki88
 
Posts: 337
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 32 times
Been thanked: 30 times

Re: JOINKEYS to find non matched records

Postby BillyBoyo » Wed Mar 30, 2016 12:10 pm

Papya013,

As Aki88 pointed out, you would not get any output with that data and those control cards, so you'd best explain how you got some output, because something you've posted is not correct.

Aki88,

Ask questions before providing code in these types of cases. Although the JOINKEYS positions are likely wrong, you have no way to guess that 1,4 should be part of the joining key, other than to guess. Get the requirement clear before providing code, please.

These users thanked the author BillyBoyo for the post:
Aki88 (Wed Mar 30, 2016 12:58 pm)
BillyBoyo
Global moderator
 
Posts: 3795
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 259 times

Re: JOINKEYS to find non matched records

Postby Papya013 » Fri Apr 01, 2016 10:03 am

I am sorry for the little confusion with the position of the fields mentioned in the code.

1234 ABCD N 9999
records in FILE1 with matching keys as
13,4,A,6,4,A,11,1,A,1,4,A
will not be present in the other file with which we will compare.

The scenario is matching keys as
(13,4,A,6,4,A,11,1,A)
there will be few records in FILE2.

So the requirement is to update the flag as 'J' on FILE1 record on not finding a matching with the join keys combination mentioned in LINE 2(which means that records unmatched only because of flag).

Kindly let me know if this is still unclear, I can try to explain in different ways.
Papya013
 
Posts: 22
Joined: Wed Nov 06, 2013 6:53 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post