|
View previous topic :: View next topic
|
| Author |
Message |
Gunapala CN
New User
Joined: 13 Oct 2016
Posts: 16
Location: India
|
|
|
|
Hi Folks,
I've come across once scenario while using Join keys :
problem statement :Compare two different length of files(length of 10 & 18), write all key matched records from dataset-2 against dataset-1 into the one output data set.
Key Filed, would get vary from 1 to till 5 length from two data sets records.
Input records :
dataset -1
| Code: |
03...F51CG
03...F54TA
03...G11AJ
03...G11DD
03...G12AC
03...G12AE
|
Note :.... are PD numbers
dataset 2
| Code: |
B4 +1.33 +0.01097
B6 +1.59 +0.00327
B9 +1.69 +0.00406
F1 +1.49 +0.00502
F3 +1.77 +0.00865
F4 +1.57 +0.00512
F5 +1.52 +0.00462
F7 +1.38 +0.01356
F8 +1.55 +0.03842
G1 +1.82 +0.01564
G2 +2.54 +0.01105
G3 +1.42 +0.03960
G4 +1.36 +0.01124
G5 +1.47 +0.01400
G6 +1.90 +0.01696
G7 +2.01 +0.02553
|
| Code: |
//SPLIT01 EXEC PGM=SORT,COND=(4,LT)
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=JKT.VXJJ0TFU.UPLOAD.FILE.P,DISP=SHR
//SORTJNF2 DD DSN=JKT.SYST.CUSTREND.NONPROM(0),DISP=SHR
//SORTOUT DD DSN=JKT.SYST.CUSTREND.NONPROM.TRIAL,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(1,1),RLSE),
// MGMTCLAS=MCNEVER,UNIT=DASD,
// DCB=(DSORG=PS,RECFM=FB,LRECL=18)
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(6,1,A)
JOINKEYS FILE=F2,FIELDS=(1,1,A)
REFORMAT FIELDS=(F2:1,18)
SORT FIELDS=COPY
/*
//* |
from above its giving duplicate rows in output data sets
Output :
| Code: |
F1 +1.49 +0.00502
F3 +1.77 +0.00865
F4 +1.57 +0.00512
F5 +1.52 +0.00462
F7 +1.38 +0.01356
F8 +1.55 +0.03842
F1 +1.49 +0.00502
F3 +1.77 +0.00865
F4 +1.57 +0.00512
F5 +1.52 +0.00462
F7 +1.38 +0.01356
F8 +1.55 +0.03842
G1 +1.82 +0.01564
G2 +2.54 +0.01105
G3 +1.42 +0.03960
G4 +1.36 +0.01124 |
Problem here is duplicates and incorrect mapping for example : dataset records is G1AJJ provided, basicaly it find first letter match from second file but its exact match would be G1.
what exactly i want is that is there any way to put varying key size while matching since this key filed expected to be varry from 1 to 5 length
Thanks for all in advance !!
Note - anyway im writing COBOL pgm to handle this but i just curios to know if any other way of doing it 
CODE' D
help people who spend their time on Your issues |
|
| Back to top |
|
 |
magesh23586
Active User
Joined: 06 Jul 2009
Posts: 213
Location: Chennai
|
|
|
|
You are matching just one byte of record in 6th position of the first dataset with
one byte of record in the first position of the second dataset
| Code: |
B
B
B
F
F
F
F
F
F
G
G
G
G
G
G
G
|
I think you need to match two bytes of record
| Code: |
JOINKEYS FILE=F1,FIELDS=(6,2,A)
JOINKEYS FILE=F2,FIELDS=(1,2,A)
|
|
|
| Back to top |
|
|
magesh23586
Active User
Joined: 06 Jul 2009
Posts: 213
Location: Chennai
|
|
|
|
Note : If your keys are in ascending order, then you may use SORTED,NOSEQCK to avoid Sorting.
| Code: |
JOINKEYS FILE=F1,FIELDS=(6,2,A),SORTED,NOSEQCK
JOINKEYS FILE=F2,FIELDS=(1,2,A),SORTED,NOSEQCK
|
|
|
| Back to top |
|
|
Gunapala CN
New User
Joined: 13 Oct 2016
Posts: 16
Location: India
|
|
|
|
| magesh23586 wrote: |
Note : If your keys are in ascending order, then you may use SORTED,NOSEQCK to avoid Sorting.
| Code: |
JOINKEYS FILE=F1,FIELDS=(6,2,A),SORTED,NOSEQCK
JOINKEYS FILE=F2,FIELDS=(1,2,A),SORTED,NOSEQCK
|
|
Thank You So much Magesh Sir
Sir, Actaully some times first file would be having data with single length, that time it wont match but it expected to have match with any of record with same starting letter.
for Eq :
File 1: G
File 2: G1FGH
with key length two this will not work!
Thanks |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Post your full rules for matching. We waste time just guessing. Include representative sample values and expected output. |
|
| Back to top |
|
|
Gunapala CN
New User
Joined: 13 Oct 2016
Posts: 16
Location: India
|
|
|
|
Sure, Definitely
File 1 Records would be :
| Code: |
F2
G1
HJ
F1GHL
G2456 |
File 2 records would be :
| Code: |
F1 1.5
F 2
HL126 2.1
G 3.1
G2HMN 4.7
G2456 3.5
G1QTY 4.9
|
EXPPECTED OUTPUT AFTER COMPRIOSN WOULD BE:
| Code: |
F 2
G1QTY 4.9
F1 1.5
G2456 3.5
|
Basically file 1 records should try to match same length of key in file 2 if not present with same key then try to find with length 4, like this till length 1 it has to match!!
Thanks you
CODE' D
learn to help people who spend time on Your issues
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
| Are the keys unique in either of the input files? |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
