View previous topic :: View next topic
|
Author |
Message |
Vsonawane
New User
Joined: 12 May 2008 Posts: 13 Location: Hartford
|
|
|
|
Hello,
I have below records in input file
Policy Number(9) Commision(4) Type(1) so LRECL is 14 bytes.
9960824240012I
9960824240012B
9890368720015I
9960824240015B
9960824240012B
9960824240012I
9890368720015I
9890368720014I
9890853380014B
9326603840015I
I require the output file to have matched duplicate records for Policy number and the Commision. so the output will look alike below mentioned.
9960824240012I
9960824240012I
9960824240012B
9960824240012B
9890368720015I
9890368720015I
Again I need to read this out file and create one more file based on 'TYPE'. Now the set of policy shuld have atleast 2 same TYPES mandatory..so the outpout will look alike
9960824240012I
9960824240012I
9960824240012B
9960824240012B
why another policy did not came out becaase it had 2 'I' but not 2 'B'.
Now here i have 1 set of policy out of 4 which came in input.
Can i get any count number in IOF having such type of policy sets?
I know this is bit tricky or twisted which i am not aware of..
Can you please guide or help to get any solution for this using SORT/ICETOOL?
Thanks,
Vikas
|
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Some questions for clarification:
What order do you want the output records in (original order, sorted order, some other order)?
Do you want two output files - one with matched duplicates and another based on TYPE? Or do you just want the output file based on TYPE?
It's not clear what you mean by "Now the set of policy shuld have atleast 2 same TYPES mandatory". Are B and I the only types? Do you want all of the records with a specific policy and commission only if one of those records has TYPE B and another has TYPE I, or do you want something else? Please clarify the "rules".
I don't know what you mean by "Can i get any count number in IOF having such type of policy sets?". What is IOF? Are you looking for a "count" of some kind? In what form? As a separate output file or what?
It would really help if you showed a better example of your input records with more variations and the complete expected output or outputs, and explained the rules for getting from input to output in terms of this better example. |
|
Back to top |
|
|
Vsonawane
New User
Joined: 12 May 2008 Posts: 13 Location: Hartford
|
|
|
|
Hi Frank,
i am trying to break up my requirement below as it is quite confusing to explain.
1) The Final Output file should have records sorted on policy Number and Commision value; however this final output file must have only duplicate policy record.
2) By Duplicate i mean I require only those policy records records which have same corresponding Commision value (some type of code) e.g Policy-1 has 2 commission values 0100 and 0200 this is not the duplicate it must have two same commission value. Then only this policy should be there in the Out file.
Policy Commision Value
XXXXX 1111
XXXXX 2222
XXXX1 1111
This are not the duplicate values
Policy Commision Value
XXXXX 1111
XXXXX 1111
XXXX1 1111
the first 2 records are duplicates and must exist in the output file.
Only those policy which are duplicates and have matching commision shuld be there in outfile.
3) The Above one is the basic requirement. Now there are only 2 policy type 'I' and 'B'. In my example i have 2 set of policies(one with 4 duplicates and other one with 2 duplicates) as explained in above two requirement:
9960824240012I
9960824240012I
9960824240012B
9960824240012B
9890368720015I
9890368720015I
Now i require only those records which have the matching set of values e,g the policy 996082424 with commision value 0012 has two matching sets one with 'I' and other with 'B', all 4 recods of this policy should come in final output file leaving the last two records as they don't have matching set with TYPe 'B'.
i Hope i am able to justify my requirements..I have tried to explain it keeping 2 sort step in mind. may be this whole thing can be done in one sort step which i might not know.
3) I am trying to find the the Count on policy type sets records in my output file. Where my input file may have millions of records.
9960824240012I
9960824240012I
9960824240012B
9960824240012B
9890368720015I
9890368720015I
9890368720015B
9890368720015B
e.g if above is my final output with 8 rows then the Policy type set count is 2.
Thanks
Vik |
|
Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
Frank,
(I)interactive (O)output (F)acility is a product that is similar to and competes with IBM's (S)pool (D)isplay and (S)earch (F)acility. It displays spooled input and output of jobs. |
|
Back to top |
|
|
vijay_pampana
New User
Joined: 19 Jan 2008 Posts: 15 Location: Hyderabad
|
|
|
|
Using ICETOOL we can acheive this requirement. May be I am giving this reply little late and by this time you may have completed your requirement but i just want to test my knowledge.
1) First select the records containing same type into one file.
i.e records with all I's into one file and records with all b's to another.
2) Using ICETOOL 'ALLDUPS' get the records which have duplicte on first 13 characters from the above files.
3) Using splice you can now match the two files and get your required output.
Thanks
Vijay |
|
Back to top |
|
|
Vsonawane
New User
Joined: 12 May 2008 Posts: 13 Location: Hartford
|
|
|
|
Hi Vijay,
The way you suggested is doable. But i am bit concerned on the 3rd point.
Say there 5 duplicates of one policy. 3 Duplicates have TYPE 'I' and 2 Duplicates have type 'B' and both these I and B will be in two seperate file. In this case i dont require the data. I would like to see the dupliacte policies only if there are exact 2 occurence of I and B each.
Thanks,
Vikas |
|
Back to top |
|
|
Vsonawane
New User
Joined: 12 May 2008 Posts: 13 Location: Hartford
|
|
|
|
Vijay, Do you have the link for the ICETOOL manual? |
|
Back to top |
|
|
vijay_pampana
New User
Joined: 19 Jan 2008 Posts: 15 Location: Hyderabad
|
|
|
|
Instead of 'ALLDUPS' use 'FIRSTDUP' in the ICETOOL JCL and create two temp files
File1: INCLUDE COND where TYP='I' and retrieve the first dup record on policy and commission
File2: INCLUDE COND where TYP='B' and retrieve the first dup record on policy and commission
Merge the above two files and get FIRSTDUP to get the required count.
If you need any more information please feel free to reply me.
Thanks
Vijay |
|
Back to top |
|
|
Vsonawane
New User
Joined: 12 May 2008 Posts: 13 Location: Hartford
|
|
|
|
Sorry to say Vijay...I am not following what you are trying to say.. |
|
Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
The following DFSORT/ICETOOL JCL will give you desired results. This will pick all duplicate records which have 2 'I' Records and 2 'B' records. If the input has only 2 'I' records per key or 2 'B' per key then we will skip such records.
Code: |
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
9960824240012I
9960824240012B
9890368720015I
9960824240015B
9960824240012B
9960824240012I
9890368720015I
9890368720014I
9890853380014B
9326603840015I
9890853390014B
9890853390014B
//T1 DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) TO(T1) ON(01,13,CH) ALLDUPS USING(CTL1)
SPLICE FROM(T1) TO(OUT) ON(1,13,CH) WITH(1,14) WITHALL USING(CTL2)
//CTL1CNTL DD *
OUTFIL FNAMES=T1,REMOVECC,BUILD=(1,14,2X),
SECTIONS=(1,13,
TRAILER3=(1,13,'$',
MIN=(14,1,ZD,M11,LENGTH=1),
MAX=(14,1,ZD,M11,LENGTH=1)))
//CTL2CNTL DD *
SORT FIELDS=(1,13,CH,A,15,1,CH,D)
OUTFIL FNAMES=OUT,INCLUDE=(15,2,ZD,EQ,29),
BUILD=(1,14)
//*
|
The output from this job is
Code: |
9960824240012I
9960824240012B
9960824240012B
9960824240012I
|
Hope this helps... |
|
Back to top |
|
|
|