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