|
View previous topic :: View next topic
|
| Author |
Message |
Mikel Terracina
New User
Joined: 02 Apr 2012
Posts: 11
Location: USA
|
|
|
|
Hello, any ideas on how to get the desired output (results on one line rather than two)?
InFile_1
| Code: |
11111 1000
22222 1001
22222 1002 |
InFile_2
| Code: |
11111 1000
22222 1001
33333 1002 |
Current Output
| Code: |
22222 1002
33333 1002 |
Desired Output
JCL
| Code: |
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,5,A,7,4,A)
JOINKEYS FILE=F2,FIELDS=(1,5,A,7,4,A)
JOIN UNPAIRED,ONLY
REFORMAT FIELDS=(F2:1,5,F1:1,5,F2:7,4,F1:7,4)
SORT FIELDS=COPY
/* |
Thanks in advance for any help.
Mike
File samples Code'd |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
It will help someone help you if you post a more complete set of some input data and the expected output.
Explain the rules for getting from the input to the output.
Why are values from 2 unrelated keys combined on the same output line? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
On what basis should they be on the same output record?
If, for this purpose, the 7,4 is a higher-order key, you have to make it so, but then it must be "unique" across your records.
If that does not do what you want, you are going to have to use GROUP in some way.
You have to explain them appearing on the same record and how you two key fields relate to each other, the existence of duplicates for either, and anything else you feel might be useful. |
|
| Back to top |
|
|
Mikel Terracina
New User
Joined: 02 Apr 2012
Posts: 11
Location: USA
|
|
|
|
My apologies. I now realize the description was pretty vague.
In a nutshell, here's my situation. I have two generational dataset's - a current generation (0) and a previous generation (-1). There are 2 keys within the files:
File layout
| Code: |
key-1 key-2
11111 1000
22222 1001 |
Sometimes between the previous generation and current generation key-2 may become associated with a different key-1:
| Code: |
previous generation (-1) file-1
11111 1000
22222 1001
22222 1002
44444 1004
44444 1005
77777 1007
77777 1008
current generation (0) - file-2
11111 1000
22222 1001
33333 1002 (key-2 associated with new key-1)
44444 1004
55555 1005 (key-2 associated with new key-1)
77777 1007
88888 1008 (key-2 associated with new key-1) |
I would like to capture records where key-2 has been associated to a different key-1, but ideally have the output written on one line as such:
(current key-1) (previous key-1) (current key-2) (optional - prev key-2)
| Code: |
33333 22222 1002
55555 44444 1005
88888 77777 1008 |
However, my current output looks like this:
| Code: |
22222 1002
33333 1002
44444 1005
55555 1005
77777 1008
88888 1008 |
Thanks,
Mike |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
So join on your 2nd key only.
Get the matches (you'll have to decided if you can get mismatches, and what you'd want to do with them if you can). OMIT the reformat records where the first key is equal and what you should be left with is perhaps what you want? |
|
| Back to top |
|
|
Mikel Terracina
New User
Joined: 02 Apr 2012
Posts: 11
Location: USA
|
|
|
|
Thanks Bill! That did the trick. Here's my JCL:
| Code: |
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(7,4,A)
JOINKEYS FILE=F2,FIELDS=(7,4,A)
REFORMAT FIELDS=(F2:1,5,F1:1,5,F2:7,4)
SORT FIELDS=COPY
OMIT COND=(1,5,CH,EQ,5,5,CH)
/* |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| No problem. Thanks for letting us know, and posting your solution. It may help someone else in the future. |
|
| Back to top |
|
|
|
|
|
