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
 

 

Retrieve matched duplicates with same key & different va

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

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Thu Apr 12, 2007 3:03 pm    Post subject: Retrieve matched duplicates with same key & different va
Reply with quote

I have a record in file 1 as
Key Value
47 111111111
47 22222222
47 33333333

I have a record in file 2 as
Key Value
47 11111111
47 22222222
47 33333333

In the output File I want the records as

Key Value_file1 Value_file2
47 111111111 222222222
47 111111111 333333333
47 222222222 111111111
47 222222222 333333333
47 333333333 111111111
47 333333333 222222222

If the key in file1 matches with the one or more records in file2 then display the key of file1 with the combination of value in file2

ie if file1 has a key 42 and value 10
file 2 has 2 records with key 42 but different values. then we need to display

file1_key(42) file1_value(10) file2_value
file1_key(42) file1_value(10) file2_value

Does this explain....

Can someone help me out

Sorry I forgot to mention one point

If a key is found only in file1 and not file2 then I donot want it in the output file
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Apr 12, 2007 8:22 pm    Post subject:
Reply with quote

Your example has the same key for every record in both files. This makes it very difficult to understand your "rules". Does every record in your files really have the same one key? Can file1 really contain duplicate keys within it? Can file2 really contain duplicate keys within it? Please show a better example of input and output with more keys showing all of the possible variations, and give a better explanation of the "rules". Also, give the RECFM and LRECL of the input files.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Thu Apr 12, 2007 8:35 pm    Post subject:
Reply with quote

Maybe this will help....
Huzefa wrote:

File1
Key Value
42 9876
42 7654
42 5432

File2
Key Value
42 9876
42 7654
42 5432

Output expected
42 9876 7654
42 9876 5432
42 7654 9876
42 7654 5432
42 5432 9876
42 5432 7654

Huzefa wrote:
If the key in file1 matches with the one or more records in file2 then display the key of file1 with the combination of value in file2

ie if file1 has a key 42 and value 10
file 2 has 2 records with key 42 but different values. then we need to display

file1_key(42) file1_value(10) file2_value
file1_key(42) file1_value(10) file2_value

Does this explain....
Back to top
View user's profile Send private message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Thu Apr 12, 2007 8:49 pm    Post subject:
Reply with quote

Yes file1 and file2 can contain duplicate keys and I think Thompson has given the rules for it

Key Value
42 100000
42 200000
43 300000
44 400000
45 500000

Key Value
42 100000
42 200000
43 400000
45 500000

So the Output should contain
Key Val_fil1 Val_fil2
42 100000 200000
42 200000 100000
43 300000 400000

PLEASESEE IF THIS HELPS
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Apr 12, 2007 8:50 pm    Post subject:
Reply with quote

William,

It doesn't help me or answer any of my questions. If it helps you, then perhaps you can provide the solution.
Back to top
View user's profile Send private message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Thu Apr 12, 2007 9:30 pm    Post subject:
Reply with quote

Ok I will pu it again..

File1 has one or more records with same key but different values

File2 has one or more records with same key but different values

If a key in File1 matches with one or more key values in File2 but both have a different value we put it in the output File with each combination of values.

If a key in File1 matches with File2 but both have same value we do not put in output file
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Apr 12, 2007 10:13 pm    Post subject:
Reply with quote

Quote:
If a key in File1 matches with one or more key values in File2 but both have a different value we put it in the output File with each combination of values.


What would the output be if the input were:

File1
42 100000
42 200000
42 300000
42 400000
43 320000
43 340000
43 360000
44 400000
45 500000
46 810000
46 830000
47 900000
47 910000
47 920000

File2
42 150000
42 250000
42 350000
42 400000
42 450000
43 310000
43 330000
44 510000
44 520000
44 530000
46 720000
46 740000
46 760000
46 780000
47 790000
Back to top
View user's profile Send private message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Fri Apr 13, 2007 8:53 am    Post subject:
Reply with quote

This is nothing but 1 to 1 search

You take one record from file one and compare with all the records in file2 with same key

If key matches and value changes you put into output file else we skip

This happened only in highlighted set

Output
KEY Val_F1 Val_F2
42 100000 150000
42 100000 250000
42 100000 350000
42 100000 400000
42 100000 450000

42 200000 150000
42 200000 250000
42 200000 350000
42 200000 400000
42 200000 450000

42 300000 150000
42 300000 250000
42 300000 350000
42 300000 400000
42 300000 450000

42 400000 150000
42 400000 250000
42 400000 350000
42 400000 450000


43 320000 310000
43 320000 330000

43 340000 310000
43 340000 330000

43 360000 310000
43 360000 330000

44 400000 510000
44 400000 520000
44 400000 530000

46 810000 720000
46 810000 740000
46 810000 760000
46 810000 780000

46 830000 720000
46 830000 740000
46 830000 760000
46 830000 780000

47 900000 790000

47 910000 790000

47 920000 790000
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Apr 13, 2007 8:42 pm    Post subject:
Reply with quote

Ah, I see. I don't know of a way to do that with DFSORT/ICETOOL.
Back to top
View user's profile Send private message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Thu Apr 19, 2007 3:56 pm    Post subject:
Reply with quote

Thanks Frank

I achieved the above task.
Sorry for al the confusion.

I used the sort utility followed by an easytrieve program to get that results.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Nov 24, 2009 2:43 am    Post subject:
Reply with quote

With z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009), you can now use JOINKEYS to do this kind of thing. For example:

Code:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//SORTOUT DD DSN=...  output file
//SYSIN    DD    *
  JOINKEYS F1=IN1,FIELDS=(1,2,A),SORTED
  JOINKEYS F2=IN2,FIELDS=(1,2,A),SORTED
  REFORMAT FIELDS=(F1:1,2,F1:3,7,F2:3,7)
  OPTION COPY
/*


For complete details on JOINKEYS and the other new functions available with the Nov, 2009 DFSORT PTF, see:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
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 SORTJOIN - Copy Matched and Unmatched... Steve Ironmonger DFSORT/ICETOOL 5 Tue Jan 17, 2017 4:26 pm
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am
No new posts Need to retrieve Julian_date Suganya87 DB2 4 Wed Aug 17, 2016 7:27 pm
No new posts Retrieve current year or month using ... vnktrrd DFSORT/ICETOOL 15 Tue Mar 15, 2016 4:14 pm


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