|
View previous topic :: View next topic
|
| Author |
Message |
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
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 |
|
 |
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
| 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 |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
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 |
|
|
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
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 |
|
|
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
| 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 |
|
|
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
| Ah, I see. I don't know of a way to do that with DFSORT/ICETOOL. |
|
| Back to top |
|
|
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
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:
www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174 |
|
| Back to top |
|
|
|
|
