Joining files to create unmatched records on output



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Joining files to create unmatched records on output

Postby adelewalsh » Mon Sep 20, 2010 9:25 pm

i,

I have 2 files I need to join/merge to create records missing from the second file based on what's in the first file.
My experience to date of Joins etc hasn't included anything like this, so I'm not sure if it's even possible.

The file layouts are very much simplyfied here but the objective is the same:
Input File 1
Product1 Combination1
Product1 Combination2
Product1 Combination3
Product2 Combination1
Product2 Combination2

Input File 2
Product1 Dealer1 Combination1
Product1 Dealer1 Combination2
Product1 Dealer1 Combination3
Product1 Dealer2 Combination1
Product1 Dealer2 Combination3
Product1 Dealer3 Combination3
Product2 Dealer1 Combination1
Product2 Dealer2 Combination2
Product2 Dealer3 Combination1

I need to create a version of file 2 that has an occurrence of each combination per dealer per product. i.e. the output should look like :
Product1 Dealer1 Combination1
Product1 Dealer1 Combination2
Product1 Dealer1 Combination3
Product1 Dealer2 Combination1
Product1 Dealer2 Combination2
Product1 Dealer2 Combination3
Product1 Dealer3 Combination1
Product1 Dealer3 Combination2
Product1 Dealer3 Combination3
Product2 Dealer1 Combination1
Product2 Dealer1 Combination2
Product2 Dealer2 Combination1
Product2 Dealer2 Combination2
Product2 Dealer3 Combination1
Product2 Dealer3 Combination2

I am thinking I would need input file 2 sorted as above, by product, dealer then combination. But then a join on Product and combination would not work correctly. I haven't yet used GROUPs, is somethign like that needed here?

Or is what I want even possible? If it takes more than 1 step, that would be fine. SYNCSORT FOR Z/OS 1.3.2.2R in use.

Many thanks in advance.
adelewalsh
 
Posts: 3
Joined: Mon Sep 20, 2010 8:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Joining files to create unmatched records on output

Postby Alissa Margulies » Mon Sep 20, 2010 10:00 pm

Hello adelewalsh,

Here is a SyncSort for z/OS job that will produce the requested output:
//SORT1  EXEC PGM=SORT       
//SORTJNF1 DD *             
PRODUCT1 COMBINATION1       
PRODUCT1 COMBINATION2       
PRODUCT1 COMBINATION3       
PRODUCT2 COMBINATION1       
PRODUCT2 COMBINATION2       
//SORTJNF2 DD *             
PRODUCT1 DEALER1 COMBINATION1
PRODUCT1 DEALER1 COMBINATION2
PRODUCT1 DEALER1 COMBINATION3
PRODUCT1 DEALER2 COMBINATION1
PRODUCT1 DEALER2 COMBINATION3
PRODUCT1 DEALER3 COMBINATION3
PRODUCT2 DEALER1 COMBINATION1
PRODUCT2 DEALER2 COMBINATION2
PRODUCT2 DEALER3 COMBINATION1
//SORTOUT DD SYSOUT=*       
//SYSOUT  DD SYSOUT=*       
//SYSIN   DD *                               
   JOINKEYS FILES=F1,FIELDS=(1,8,A)         
   JOINKEYS FILES=F2,FIELDS=(1,8,A)         
   REFORMAT FIELDS=(F1:1,9,F2:10,8,F1:10,12)
   SORT FIELDS=(1,29,CH,A)                   
   SUM FIELDS=NONE                           
/*                                           
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times

Re: Joining files to create unmatched records on output

Postby meowmeow » Tue Nov 02, 2010 6:57 pm

Hi Alissa,

I am reading your Joinkeys codes in this site. If you would have time could you kindly explain how this works:

JOINKEYS FILES=F1,FIELDS=(1,8,A)
JOINKEYS FILES=F2,FIELDS=(1,8,A)
REFORMAT FIELDS=(F1:1,9,F2:10,8,F1:10,12)
SORT FIELDS=(1,29,CH,A)

I'm trying to use this as a basis for getting the specific records that are in File2 but not in File1:
File1:(key: column 3-13)
12AAAAAAAAAAAAA
90BBBBBBBBBBBBBBB

File2:(key: column 1-13)
AAAAAAAAAAAAA
CCCCCCCCCCCCC
DDDDDDDDDDDDD

I'm using the below code but only gets this output:
AAAAAAAAAAAAA
CCCCCCCCCCCCC
DDDDDDDDDDDDD
   JOINKEYS FILES=F1,FIELDS=(3,13,A)         
   JOINKEYS FILES=F2,FIELDS=(1,13,A)         
   JOIN UNPAIRED,F2,ONLY
   REFORMAT FIELDS=(F2:1,13)
   OPTION COPY
   OUTREC FIELDS=(1,13)
meowmeow
 
Posts: 8
Joined: Thu Feb 18, 2010 5:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Joining files to create unmatched records on output

Postby Alissa Margulies » Tue Nov 02, 2010 7:58 pm

I have submitted a test job with the exact same SYSIN you have specified above and the same sample records.
I then submitted a second test job with your sample records, but I simplified the SYSIN statements as follows:
//SORT1  EXEC PGM=SORT             
//SORTJNF1 DD *                   
12AAAAAAAAAAAAA                   
90BBBBBBBBBBBBBBB                 
//SORTJNF2 DD *                   
AAAAAAAAAAAAA                     
CCCCCCCCCCCCC                     
DDDDDDDDDDDDD                     
//SORTOUT DD SYSOUT=*             
//SYSOUT  DD SYSOUT=*             
//SYSIN   DD *                     
  JOINKEYS FILES=F1,FIELDS=(3,13,A)
  JOINKEYS FILES=F2,FIELDS=(1,13,A)
  JOIN UNPAIRED,F2,ONLY           
  SORT FIELDS=COPY                     
/*                                 

Both jobs got the following results:
CCCCCCCCCCCCC
DDDDDDDDDDDDD

Please send me your complete job listing offline at alissa.margulies@syncsort.com if you require further assistance.
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times

Re: Joining files to create unmatched records on output

Postby meowmeow » Thu Nov 04, 2010 8:34 pm

Thanks Alissa your code had worked mine didn't. Thanks a bunch! :)
meowmeow
 
Posts: 8
Joined: Thu Feb 18, 2010 5:37 pm
Has thanked: 0 time
Been thanked: 0 time


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post