|
View previous topic :: View next topic
|
| Author |
Message |
Kiruthi R
New User
Joined: 21 Sep 2011
Posts: 4
Location: India
|
|
|
|
Hi,
I have one master file.I need to generate a report ,sort key is name ,city,branch. I have to report total net amount,unique total account numbers,UNIQUE cusips count for each group.
SAMPLE REPORT:
| Code: |
SECTION 1
NAME1 CITY1 BRANCH1 ACCTNUM1 CUSIP1 10,000
NAME1 CITY1 BRANCH1 ACCTNUM2 CUSIP1 5,000
NAME1 CITY1 BRANCH1 ACCTNUM2 CUSIP2 5,000
TOTAL AMOUNT 20,000
ACCT COUNT 2
CUSIP COUNT 2
SECTION 2
NAME2 CITY2 BRANCH2 ACCTNUM3 CUSIP3 10,000
NAME2 CITY2 BRANCH2 ACCTNUM3 CUSIP3 5,000
NAME2 CITY2 BRANCH2 ACCTNUM4 CUSIP4 5,000
TOTAL AMOUNT 20,000
ACCT COUNT 2
CUSIP COUNT 2 |
Im able to get all the details except UNIQUE count for acct and cusip in each group. UNIQUE keyword display the count for full report.Is there any way to get unique counts within section.Please advise. |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Please show what you have so far. |
|
| Back to top |
|
|
Kiruthi R
New User
Joined: 21 Sep 2011
Posts: 4
Location: India
|
|
|
|
Hi,
Please see this..
| Code: |
SORT FIELDS=(1,10,CH,A,11,6,CH,A,17,6,CH,A)
OUTFIL FNAMES=REPORT,
SECTIONS=(1,22,SKIP=2L,
HEADER3=(3:X,/,
3:'NAME',16:'CITY',25:'BRANCH',33:'ACCT NUM',48:'CUSIPS',57:'AMOUNT',/,
3:'----',16:'----',25:'------',33:'------'48:'------',57:'------'),
TRAILER3=(
3:'Total Amount =',
18:TOT=(52,8,BI,EDIT=(IIIIIIIIT.TT)),/,
3:'ACCT COUNT =',
18:COUNT=(?),/,
3:'CUSIP COUNT =',
18:COUNT=(?))),
OUTREC=(3:1,10,
17:11,6,
26:17,6,
34:23,12,
49:35,6,
58:52,8)
|
With this, If duplicate account numbers(or cusips) are present,i couldnt get unique count. UNIQUE keyword displays full report count at the end of the report. But i need two counts (acct num and cusips,without duplicates) to be printed at the end of each section. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Next time please use the Code tags for any data for which you want to preserve the formatting. You are more likely to get an answer if we can read the stuff....
I'm not sure that you can directly do what you want. The COUNT will be for each input record. If you have multiples on your input, they will be added to the count.
Also, you don't seem to have either account or cusip in your sort, so I'm not even sure what magic you were expecting to happen.
Questions remain: If there are multiple use of the same cusip in an account, how should it be counted; if there is multiple use of the same cusip across accounts, how should it be counted; and vice versa for both. |
|
| Back to top |
|
|
Kiruthi R
New User
Joined: 21 Sep 2011
Posts: 4
Location: India
|
|
|
|
Thanx for the reply.Yes. Thats what confusing.
So to get unique count,u mean to say there is no direct way. Is that efficient to to use temporary files.
Sort the file using sort key store it in tempfile1.
Sort the master file along wit acctnum.Remove duplicates.get count.tempfile2
Sort the master file along with cusip. Remove duplicates.tempfile3
Merge all using splice. tempfile4.
Then using cobol program,create report as per the requirement.(Requirement is : From master file , group the records using name,branch and city and report the total amount,unique count for cusip and acct-number at the end of each group in the report file.)
Is this ok or any other method to achieve this.?Please help how to get the results in indirect way. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
I am completely open to correction on this, but from what I understand, the COUNT is going to be of the records, not of the "unique" items.
When the experts are in later, San Jose working-hours at the latest, you'll know for certain.
If it is possible, you'll certainly need to sort on the account and cusip anyway.
If you are considering a seperate report program, just your existing file with the above sorts could be enough - depending on the answers to my previous questions.
If you want accouts per break, and cusips treated as unique within account, then you would need no further than the normal sort of processing. If you want a strictly unique count of cusips (so even if present in more than one account per break, only count as one) you'd need to hold them in a table, and add each one if not already present in the table. The vice versa is higly unlikely to be your requirement, but if so, the same applies with the names changed (yes, I do know there is a posh phrase for that). And the sort key sequence, of course.
I do (strongly) suspect that along the lines you have already mentioned, a sort/tool solution is possible, but you'll need to provide clear answers. |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
Kiruthi R,
You don't have to send a Private message with the same question. Since you want the unique counts, it cannot be done in single pass of data. Here is a DFSORT/ICETOOL JCL which will give you the desired results. I assumed that your input is RECFM=FB and LRECL=80
| Code: |
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DISP=SHR,DSN=Your Input File
//T1 DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//REPORT DD SYSOUT=*
//TOOLIN DD *
SORT FROM(IN) USING(CTL1)
SORT FROM(T1) USING(CTL2)
//CTL1CNTL DD *
SORT FIELDS=(1,34,CH,A),EQUALS
OUTFIL FNAMES=T1,IFOUTLEN=81,
IFTHEN=(WHEN=INIT,OVERLAY=(81:C'0',SEQNUM,8,ZD,RESTART=(1,34))),
IFTHEN=(WHEN=(82,8,ZD,EQ,1),OVERLAY=(81:C'1'))
//*
//CTL2CNTL DD *
SORT FIELDS=(1,22,CH,A,35,6,CH,A),EQUALS
OUTREC IFOUTLEN=82,IFTHEN=(WHEN=INIT,
OVERLAY=(82:C'0',1,22,35,6,SEQNUM,8,ZD,RESTART=(83,28))),
IFTHEN=(WHEN=(111,8,ZD,EQ,1),OVERLAY=(82:C'1'))
OUTFIL FNAMES=REPORT,
BUILD=(03:01,10,15:11,6,24:17,6,33:23,12,48:35,6,
57:52,8,BI,EDIT=(IIIIIIIT.TT),80:X),
SECTIONS=(1,22,SKIP=2L,
HEADER3=(03:X,/,
03:'NAME',17:'CITY',24:'BRANCH',33:'ACCT NUM',
48:'CUSIPS',60:'AMOUNT',/,
03:10C'-',15:6'-',24:6'-',33:12'-',48:6'-',57:11'-'),
TRAILER3=(03://,
03:'TOTAL AMOUNT =',
18:TOT=(52,8,BI,EDIT=(IIIIIIIIT.TT)),/,
03:' ACCT COUNT =',
18:TOT=(81,1,ZD,M10,LENGTH=12),/,
03:'CUSIP COUNT =',
18:TOT=(82,1,ZD,M10,LENGTH=12)))
//* |
The output from this job would be something like this
| Code: |
NAME CITY BRANCH ACCT NUM CUSIPS AMOUNT
---------- ------ ------ ------------ ------ -----------
NAME1 CITY1 BRANC1 ACCTNUM1 CUSIP1 10000.00
NAME1 CITY1 BRANC1 ACCTNUM2 CUSIP1 5000.00
NAME1 CITY1 BRANC1 ACCTNUM2 CUSIP2 5000.00
TOTAL AMOUNT = 20000.00
ACCT COUNT = 2
CUSIP COUNT = 2
NAME CITY BRANCH ACCT NUM CUSIPS AMOUNT
---------- ------ ------ ------------ ------ -----------
NAME2 CITY2 BRANC2 ACCTNUM3 CUSIP3 14000.00
NAME2 CITY2 BRANC2 ACCTNUM3 CUSIP3 6000.00
NAME2 CITY2 BRANC2 ACCTNUM4 CUSIP4 8000.00
NAME2 CITY2 BRANC2 ACCTNUM4 CUSIP5 8000.00
TOTAL AMOUNT = 36000.00
ACCT COUNT = 2
CUSIP COUNT = 3
|
|
|
| Back to top |
|
|
Kiruthi R
New User
Joined: 21 Sep 2011
Posts: 4
Location: India
|
|
|
|
Thanx Kolusu. Its working perfectly. Sorry for the late reply.So this is the logic to get two unique counts. Thanx a ton.
Thanx Bill. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
