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.
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:
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)
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?