Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Matching and unmatching records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
scdinesh
Warnings : 1

New User


Joined: 06 Sep 2006
Posts: 37
Location: United States

PostPosted: Tue Feb 07, 2017 12:41 am    Post subject: Matching and unmatching records
Reply with quote

Guys, Appreciate your help me on following requirement:
I've 2 input files(not in sorted order) , compare both files with matching key as(1st 4 digits) and create 4 output files: Output File1 with matching records only from input file1 and output File 2 with matching records only from input file2(Although key matches , I need to validate the remaining records latter from the files separately)
Output file 3 with unmatched records from input file1 and Output file 4 with unmatched records from input file2.

Sample Records
Input File1:
2222Keith
1111David
4444Sam
3333Rohit

Input File2:
3778Ruban
3333Rizwan
4444Andrew
5555Travis

Comparison upon key: S.No(1st 4 digit)
Output File1:(match from input File1)
4444Sam
3333Rohit

Output File2:(match from input File2)
3333Rizwan
4444Andrew

Output File3:(un match from input File3)
2222Keith
1111David

Output File4:(unmatch from input File4)
3778Ruban
5555Travis

Thanks for your help
Back to top
View user's profile Send private message

sergeyken

New User


Joined: 29 Apr 2008
Posts: 70
Location: Maryland

PostPosted: Tue Feb 07, 2017 1:59 am    Post subject:
Reply with quote

RTFM:

Code:

JOINKEYS FILE=F1,...
JOINKEYS FILE=F2,...
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=...,FILL=...
SORT FIELDS=...
OUTFIL FNAMES=OFILE1,INCLUDE=...,BUILD=...
OUTFIL FNAMES=OFILE2,INCLUDE=...,BUILD=...
OUTFIL FNAMES=OFILE3,INCLUDE=...,BUILD=...
OUTFIL FNAMES=OFILE4,INCLUDE=...,BUILD=...
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7306

PostPosted: Tue Feb 07, 2017 2:39 am    Post subject: Reply to: Matching and unmatching records
Reply with quote

No need for FILL=, use the match-marker (?, a question-mark, in the REFORMAT record, B=both, 1=file1 only, 2=file2 only).

The SORT would be FIELDS=COPY.
Back to top
View user's profile Send private message
scdinesh
Warnings : 1

New User


Joined: 06 Sep 2006
Posts: 37
Location: United States

PostPosted: Tue Feb 07, 2017 2:43 am    Post subject: Matching and unmatching records
Reply with quote

Thanks sergeyken for quick response...
Assume my record length is set to 80 , with first 4 digits as key
may I know what is the value I can add in 'Include =' . I don't have any specific condition to filter out the records.
Also please help me on REFORMAT FIELDS=...,FILL=...
Back to top
View user's profile Send private message
scdinesh
Warnings : 1

New User


Joined: 06 Sep 2006
Posts: 37
Location: United States

PostPosted: Tue Feb 07, 2017 2:55 am    Post subject:
Reply with quote

Hi Bill,
Thanks for the reply!!!
Please advise how can I specify.

JOINKEYS FILES=F1,FIELDS=(1,4,A)
JOINKEYS FILES=F2,FIELDS=(1,4,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,80,?)
OPTION COPY
OUTFIL FNAMES=OFILE1,INCLUDE=(81,1,CH,EQ,C'B'),
BUILD=(1,80)
OUTFIL FNAMES=OFILE2,INCLUDE=(81,1,CH,EQ,C'1'),
BUILD=(1,80)
OUTFIL FNAMES=OFILE3,INCLUDE=(81,1,CH,EQ,C'2'),
BUILD=(1,80)

Note:
B=both -> In my case I need to write matching/unmatching records into separate files
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2238
Location: @my desk

PostPosted: Tue Feb 07, 2017 3:11 am    Post subject:
Reply with quote

scdinesh,

Since you need to write the entire record area from both the inputs, your REFORMAT should include both F1 and F2 fields. Something like,
Code:
REFORMAT FIELDS=(F1:1,80,F2:1,80,?)

Your OFILE1 (matched records from input1) and OFILE2 (matched records from input2) should both INCLUDE 'B' records, but write only F1 and F2 records respectively into each of those outputs.

Similarly for OFILE3 (unmatched from input1) and OFILE4 (unmatched from input2) write relevant fields to output in the BUILD.
Back to top
View user's profile Send private message
scdinesh
Warnings : 1

New User


Joined: 06 Sep 2006
Posts: 37
Location: United States

PostPosted: Tue Feb 07, 2017 9:09 pm    Post subject:
Reply with quote

Thanks Arun for the help!!!

"Your OFILE1 (matched records from input1) and OFILE2 (matched records from input2) should both INCLUDE 'B' records, but write only F1 and F2 records respectively into each of those outputs"

In my case I want to write only F1 records into OFILE1 upon condition , how does it know it should write only F1 records? (as we have mentioned both F1 and F2 in 'Reformat fields'). Please help
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2238
Location: @my desk

PostPosted: Tue Feb 07, 2017 9:33 pm    Post subject:
Reply with quote

Quote:
In my case I want to write only F1 records into OFILE1 upon condition , how does it know it should write only F1 records?
The BUILD parameter in each OUTFIL group lets you choose specific fields from the REFORMAT-ted combined record (161 bytes in your case).
It is just a matter of writing first 80 bytes of the reformatted record into your first OUTFIL and the next 80 bytes of the reformatted record into your second OUTFIL.
Back to top
View user's profile Send private message
scdinesh
Warnings : 1

New User


Joined: 06 Sep 2006
Posts: 37
Location: United States

PostPosted: Wed Feb 08, 2017 8:47 pm    Post subject: Reply to: Matching and unmatching records
Reply with quote

Thanks Sergeyken,Bill and Arun
Back to top
View user's profile Send private message
scdinesh
Warnings : 1

New User


Joined: 06 Sep 2006
Posts: 37
Location: United States

PostPosted: Wed Feb 08, 2017 11:30 pm    Post subject:
Reply with quote

Hi,
Please see if there is anything wrong in the below sort upon my above requirement? am seeing empty records in all 4 output files.

JOINKEYS F1=CONCUR,FIELDS=(1,4,A)
JOINKEYS F2=LOOKUP,FIELDS=(1,4,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,80,F2:1,80,?)
SORT FIELDS=COPY
OUTFIL FILES=01,INCLUDE=(81,1,CH,EQ,C'B'),BUILD=(1,80)
OUTFIL FILES=02,INCLUDE=(81,1,CH,EQ,C'B'),BUILD=(1,80)
OUTFIL FILES=03,INCLUDE=(81,1,CH,EQ,C'1'),BUILD=(1,80)
OUTFIL FILES=04,INCLUDE=(81,1,CH,EQ,C'2'),BUILD=(1,80

Thanks
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2238
Location: @my desk

PostPosted: Wed Feb 08, 2017 11:32 pm    Post subject:
Reply with quote

Your match marker position is 161 and NOT 81.

Plus you need to fix your BUILDs as suggested earlier.
Back to top
View user's profile Send private message
John Del

New User


Joined: 27 Apr 2012
Posts: 24
Location: NY

PostPosted: Thu Feb 09, 2017 2:10 am    Post subject:
Reply with quote

scdinesh -

I would suggest that you first just write the reformatted output to one dataset, so that you can see how the join operation is outputting the joined data in relation to the match marker value(s). That might help you to understand how to write your INCLUDE conditions and BUILD parameters for the OUTFIL files.

/*
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us