Posted: Mon Oct 05, 2020 9:23 pm Post subject: COUNT and SUM of fields using ICETOOL
Hello,
I scanned the board before posting the query.
Query: Is it possible to get COUNT and SUM of fields in ICETOOL just like we have in DB2. I know it is possible in DFSORT and also we have OCCURS in ICETOOL to get count of grouped fields using VALECNT. However, I am struggling to get summation of amounts against same set of grouped fields.
For example:Imagine you have a file with LRECL=80. You have employee ID as first 10 character followed by 2 byte state code (MH, JK etc) followed by 2 byte numeric field depicting years of experience.
So output should have State, Number of employees, Consolidated experience.
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
Posted: Mon Oct 05, 2020 11:24 pm Post subject: Re: COUNT and SUM of fields using ICETOOL
pai_sunil_bs wrote:
For example:Imagine you have a file with LRECL=80. You have employee ID as first 10 character followed by 2 byte state code (MH, JK etc) followed by 2 byte numeric field depicting years of experience.
So output should have State, Number of employees, Consolidated experience.
I am no comparison to you Kolusu in terms of knowledge!
What I meant to say was that I am aware that my requirement can be met through SORT. I am also aware that ICETOOL invokes SORT.
I am only curious to know if ICETOOL operators within TOOLIN itself give us the flexibility to get count and amount together (without having to code additional DFSORT statements within XXXXCNTL).
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
Posted: Tue Oct 06, 2020 3:23 pm Post subject:
pai_sunil_bs wrote:
I am no comparison to you Kolusu in terms of knowledge!
It is NOT the question of who is knowledgeable here. Being knowledgeable is relative. Everyone is knowledgeable in their own way.
pai_sunil_bs wrote:
What I meant to say was that I am aware that my requirement can be met through SORT. I am also aware that ICETOOL invokes SORT.
I am only curious to know if ICETOOL operators within TOOLIN itself give us the flexibility to get count and amount together (without having to code additional DFSORT statements within XXXXCNTL).
ICETOOL's DISPLAY Operator can give you total and counts, however you need to have the file sorted on the STATE prior to invoking the DISPLAY operator. So that would be 2 passes of the data which is quite unnecessary. _________________ Kolusu
www.linkedin.com/in/kolusu
Thanks Kolusu. Using DISPLAY ON "experience" field, I put a BREAK on the "STATE" field and then used BCOUNT and BTOTAL. I am getting the desired results. Thanks for your guidance.
But what I found is that the output is not user friendly.
It comes something like this
MH
(40,2,ZD)
+0000000012
COUNT0000000001
TOTAL
+0000000012
What I was looking for
MH 00001 00012
i.e. state, count, total like a normal report
I tried HEADER, BTITLE etc but could not the desired output.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum