|
View previous topic :: View next topic
|
| Author |
Message |
gayathrinag
New User
Joined: 16 Oct 2008
Posts: 37
Location: chennai
|
|
|
|
Hello,
I have two files i need to compare and join them based on key values.
Input 1
--------
| Code: |
04659993ACPK0000E0104659993AC
04659993ACPK0000E0204659617AB
04659993ACPK0000E0204659995
04659993ACPK0000E0304659996
04659993ACPK0000E0204659997AB |
input2
---------
| Code: |
04659993ACPK0000E0104659993AC
04659993ACPK0000E0204659617AB
04659993ACPK0000E0204659997AB
04659993ACPK0000E0268229953AA
04659993ACPK0000E0304659996 |
Required Output
------------------
| Code: |
04659993ACPK0000E0104659993AC04659993ACPK0000E0104659993AC
04659993ACPK0000E0204659617AB04659993ACPK0000E0204659617AB
04659993ACPK0000E0204659995 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$
04659993ACPK0000E0304659996 04659993ACPK0000E0304659996
04659993ACPK0000E0204659997AB04659993ACPK0000E0204659997AB
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$04659993ACPK0000E0268229953AA
|
I have used the following jcl sort to get this done.
| Code: |
JOINKEYS FILES=F1,FIELDS=(1,10,A,18,12,A),
SORTED,NOSEQCK
JOINKEYS FILES=F2,FIELDS=(1,10,A,18,12,A),
SORTED,NOSEQCK
JOIN UNPAIRED,F1,F2
SORT FIELDS=COPY
REFORMAT FIELDS=(F1:30,F2:1,30),FILL=C'$' |
After running this sort the below output has come. And its not what i have expected.
Wrong Output
---------------
| Code: |
04659993ACPK0000E0104659993AC04659993ACPK0000E0104659993AC
04659993ACPK0000E0204659617AB04659993ACPK0000E0204659617AB
04659993ACPK0000E0204659995 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$04659993ACPK0000E0204659997AB
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$04659993ACPK0000E0268229953AA
04659993ACPK0000E0304659996 04659993ACPK0000E0304659996
04659993ACPK0000E0204659997AB$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ |
Note: I dont want to sort my input files. This is very important. Is there any other way other than sort is also fine.
Can anyone help me in this request.
Thank You
Code'd |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
If your second key is not in order, how do you expect it to work?
Why don't you want to SORT? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Quote: |
| I dont want to sort my input files. This is very important. Is there any other way other than sort is also fine. |
And why do you not want them to be sorted?
Try and make a use of SAS |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
How/why do you expect to get the "desired" output given the "rules" you have specified?
If you explain what these fields are we may have a suggestion. I do not understand the situation  |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Also the Title and the content seem to differ for me
When you don't need sort what is the expected output? |
|
| Back to top |
|
|
gayathrinag
New User
Joined: 16 Oct 2008
Posts: 37
Location: chennai
|
|
|
|
Rohit and Bill,
My second input file will be in the provided order and it should not be sorted, so I need to match up both these files and get the desired output without missing the order of records.
Please let me know if we have any possible solution for this using SORT or ICETOOL
Thanks |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Could you show the expected output within code tags? |
|
| Back to top |
|
|
gayathrinag
New User
Joined: 16 Oct 2008
Posts: 37
Location: chennai
|
|
|
|
This is my desired output.
| Code: |
04659993ACPK0000E0104659993AC04659993ACPK0000E0104659993AC
04659993ACPK0000E0204659617AB04659993ACPK0000E0204659617AB
04659993ACPK0000E0204659995 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$
04659993ACPK0000E0304659996 04659993ACPK0000E0304659996
04659993ACPK0000E0204659997AB04659993ACPK0000E0204659997AB
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$04659993ACPK0000E0268229953AA
|
Example:
File 1 :
| Code: |
Company Level Sub Company
A 01 B
A 02 C
A 02 D
A 03 E
A 01 X
A 02 Y
B 01 H |
File 2 :
| Code: |
Company Level Sub Company
A 01 B
A 02 D
A 03 G
A 02 Y
B 01 H
C 01 Z |
i have compare these file without sorting because company and sub company.
For company A i have B is the first level sub company and C,D,E 2 and 3rd level company's for A.
Under the same A i have X as the first level company and Y as the 2nd level.
My screen has to display in the same order when i inquire "A" it should display "B" as the first level
and C,D,E as 2nd and 3rd level
This is how the screen will look like
Screen
------
| Code: |
A 01 B
A -02 C
A -02 D
A -03 E
A 01 X
A -02 Y |
For this reason only i dont want to sort my file
and my output after comparing these two file should be
Desired Output After sort
| Code: |
-------------------------
File 1 File 2
A 01 B A 01 B
A 02 C $$$$$$$ ( Because "C" is not present in file 2 as sub company level hence i need $ )
A 02 D A 02 D
A 03 G A 03 G
A 01 X $$$$$$$ ( Because"X" is not present in file 2 as sub company level hence i need $)
A 02 Y A 02 Y
B 01 H B 01 H
$$$$$$$ C 01 Z (Because "C" is not present in file 1 as company level hence i need $) |
Please help me to resolve this issue. If any other suggestion other than sort is also could help us in solving this issue.
Thank You for helping me out.
Code'd again |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Can you please sort the files and get the output as you expect, we will see next step how to retain the same order on your screen?
Also, please let us know what logic is place for screen? |
|
| Back to top |
|
|
gayathrinag
New User
Joined: 16 Oct 2008
Posts: 37
Location: chennai
|
|
|
|
Hi Their,
I mentioned the screen layout in my earlier post.
Screen
------
A 01 B
A -02 C
A -02 D
A -03 E
A 01 X
A -02 Y
For this reason only i should not sort my files.
Can you please let me know if you have any other suggestions.
Thank You |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
If your data is not in sequence on the keys then how do you export JOINKEYS to work?
If there is some strange reason that you can't SORT the data so that the keys are in sequence, then you can't use JOINKEYS for the data you have shown.
COBOL? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
How do you get ?
| Code: |
Screen
------
A 01 B
A -02 C
A -02 D
A -03 E
A 01 X
A -02 Y |
It basically should be,
| Code: |
Screen
------
A 01 B
A -02 C
A -02 D
A -03 G
A 01 X
A -02 Y |
Also please tell us more on step by step approach on how the data is shown on screen? what do you do next on the output file after the outer join? |
|
| Back to top |
|
|
gayathrinag
New User
Joined: 16 Oct 2008
Posts: 37
Location: chennai
|
|
|
|
Please ignore my previous example you can take look at below example.
Are other example is
Technology Escalation Level Mgr.Name
Mainframe 1 A
Mainframe 2 B (COBOL)
Mainframe 2 C (Eazytrieve)
Mainframe 3 D (Eazytrieve)
Mainframe 2 Z (DB2)
Java 2 X (J2EE)
i have the technology which is again mapped to escalation level with mgr list. I cannot theese details with Technology and escalation level. Because if i sort with these keys i will get all 2nd level esclation together
which is wrong because i have 3rd level escalation for Eazytrieve.
Please let me know my example is clear.
Thank You |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Humour me, and try this. Your F2 looks to be in sequence, but I've taken off the NOSEQCK temporarily so you can check.
| Code: |
JOINKEYS FILES=F1,FIELDS=(1,10,A,18,12,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A,18,12,A),
SORTED
JOIN UNPAIRED,F1,F2
SORT FIELDS=COPY
REFORMAT FIELDS=(F1:30,F2:1,30),FILL=C'$' |
Please learn how to use the Code tags. |
|
| Back to top |
|
|
gayathrinag
New User
Joined: 16 Oct 2008
Posts: 37
Location: chennai
|
|
|
|
I did try removing the NOSEQNCK and my F2 is not in Sorted. I have given the Eazytrieve and Cobol is just convey my requirements. In my original file i dont have COBOL, EAZYTRIEVE rather i only have Mgr Name and the Escaltion with Technology called mainframe or Java.
Thank You |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
| gayathrinag wrote: |
File 1 :
| Code: |
Company Level Sub Company
A 01 B
A 02 C
A 02 D
A 03 E
A 01 X
A 02 Y
B 01 H |
File 2 :
| Code: |
Company Level Sub Company
A 01 B
A 02 D
A 03 G
A 02 Y << How does it go to 2nd group?
B 01 H
C 01 Z |
Desired Output After sort
| Code: |
-------------------------
File 1 File 2
A 01 B A 01 B
A 02 C $$$$$$$ ( Because "C" is not present in file 2 as sub company level hence i need $ )
A 02 D A 02 D
A 03 G A 03 G
A 01 X $$$$$$$ ( Because"X" is not present in file 2 as sub company level hence i need $)
A 02 Y A 02 Y
B 01 H B 01 H
$$$$$$$ C 01 Z (Because "C" is not present in file 1 as company level hence i need $) |
|
How do you distinguish file 2 records that belong to a different group? Is 3 the highest sub level? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
I think of one approach, see if this looks as expected.
1) Assign SEQNUM to all the records at last in the input files and get the new output files
2) Then do a sorting based on the key provided by you and make a full outer join as per your requirement
3) The output of full outer join again needs to be sorted based upon the step #1 i.e. based on the SEQNUM to retain back your original order.
Kolusu/Bill, Need your thoughts on this, I might be missing something in this approach. |
|
| Back to top |
|
|
|
|
