|
View previous topic :: View next topic
|
| Author |
Message |
venkata.ravi
New User
Joined: 09 May 2005
Posts: 30
Location: Hyderabad
|
|
|
|
Hi Trinadh,
In addition to above 2 example, Here is another example, Code handling only 2 different values for COL12,
Input
| Code: |
COL11 COL12
-----------
012345 DEF
000033 ABC
000044 PQR |
Output
| Code: |
COL11 COL12
-----------
012422 ABC,DEF <<-- Count for all the records is coming but, the COL12 value (PQR) for 3rd identical record is missing. Can we make it dynamic, so that it can handle around 10 identical records. |
|
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Code: |
RQ#1: Sort the records in a user specific order for COL1.
Required order of COL1 is ** EVALUATIONE, POPULAR, CONTRIBUTION, TOSS and others if any **
RQ#2: Add COL11 together if all other COL's (COL1 to COL10) are identical.
RQ#3: Add a new row for every set of COL1, COL2 and COL3 and give a name to the set as 'TOTAL : ' SUM(COL11)
Also add an empty row after every TOTAL. This is for better readability of the report.
The string 'TOTAL' starts at position 110 and the SUM(COUNT) starts from position at 121. |
The above is very easy and can be done in one step.
INREC to prepare an additional field for the "outsort".
SORT on the "outsort" first, then the rest of the data to the amount.
Use OUTFIL with NODETAIL, with SECTIONS, with two sets of description for your two different control breaks. Use TOT to get the total.
That leaves the "concatenation" of COL 12. Work on the above whilst getting 100% clear on what you want for the "concatenation".
You now, "of course", want to "consolidate" the COL 12 of up to 10 records?
You haven't given much of an example of what might genuinely exist in the existing 50 bytes, where you say probably 1-25 bytes are used.
Can there be "embedded" blanks? Leading blanks? Or just trailing blanks?
| Code: |
A, B, C, D, E, A
A, B, C, D, E
|
With the former, you know what each record had, and you know that the total was consolidated from six records. With the latter, you have no clue either how many records, or what their original values were, other than the first element. Is that what you really want?
What about a COL 12 which is blank? How do you want that to be? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Looking at -3nadh's code, it is only dealing with one instance of COL 12 for multiple records. If you understood the Control Cards you would know that, and not ask about what to change for that.
Bear in mind that unless your data already contains the entire groups (ie when sorted, no additional records will be added to a group, or to put it another ways, no groups are "consolidated" through a sort taking place) then this technique requires an additional SORT, which your client or your company are paying for the resources for. |
|
| Back to top |
|
|
venkata.ravi
New User
Joined: 09 May 2005
Posts: 30
Location: Hyderabad
|
|
|
|
Here are the COL12 looks like
Length - 50 bytes
It can never be blank.
It can never have leading spaces, but can have embedded and/or trailing spaces. Trailing spaces need to be truncated.
Similiar COL12 values need not to consider, but their corresponding COL11 should be summed up.
| Quote: |
| Code: |
A, B, C, D, E, A
A, B, C, D, E |
|
Yes, COL11 values for the 6 records should be summed up, but the COL12 values should not be repeated. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| OK, let us know when you have the outsort and totals working, and there'll be help with the consolidation. |
|
| Back to top |
|
|
venkata.ravi
New User
Joined: 09 May 2005
Posts: 30
Location: Hyderabad
|
|
|
|
Hi Bill,
What do you mean by "outsort". Do you refer Req#1 as outsort ?
| Quote: |
RQ#1: Sort the records in a user specific order for COL1.
Required order of COL1 is ** EVALUATIONE, POPULAR, CONTRIBUTION, TOSS and others if any ** |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Yes. They are to be sorted "out of sequence", so an "outsort"... |
|
| Back to top |
|
|
venkata.ravi
New User
Joined: 09 May 2005
Posts: 30
Location: Hyderabad
|
|
|
|
Hi Bill,
I'm able to "outsort" with the control card provided by trinadh. And the Total and sub-totals are also working fine with the sort provided by trinadh.
Only thing left is to handle COL12 values . |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
That solution has three SORTs in three steps.
It can be done in one SORT in one step.
Which do you think your client would prefer to be paying for? |
|
| Back to top |
|
|
venkata.ravi
New User
Joined: 09 May 2005
Posts: 30
Location: Hyderabad
|
|
|
|
Hi Bill,
If it can be done in single step, it would be really great. Can you please provide me that sort card ? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Did you read this? Or the earlier one?
With INREC, prepare a field appended to your FB which contains D, G, J, M for your four "outsorted" values respectively and which otherwise contains X.
The reason for the "gaps" in the letters is to allow for easy amendments.
Then SORT on the new field and then the rest of the key that you want, so that you get your "outsort" and then within that outsort you get the required order.
Then use OUTFIL with SECTIONS and TRAILER3.
| Code: |
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(p,m, start and length of your key for the TOTAL
TRAILER3=(c:C'TEXT YOU WANT',TOT=(p,m,ZD),/),
p,m, start and length of your sub-total
TRAILER3=(p,m, start and length of data before sub-tot
TOT=(p,m,ZD),
p,m start and length of data before sub-tot))
|
I've not even really tried to get the syntax right, you've got to do some work yourself.
Then you just need the extended field for the summed values, and the "consolidation", but get the above working first. |
|
| Back to top |
|
|
venkata.ravi
New User
Joined: 09 May 2005
Posts: 30
Location: Hyderabad
|
|
|
|
Hi Bill,
Below are the 2 pieces of codes I have amended to the outsort using INREC in a single step.
This code returns sub totals for each row. But the actual row reflects empty, Actual data is missing but the sub totals for each totals are coming in order.
| Code: |
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,121,
TRAILER3=(122:C'TOTAL : ',TOT=(122,6,ZD),/),
131,7) |
This piece of code returns an error message, " WER116A THE FOLLOWING H/T LINE IS GT LRECL:
First record after outsort displays here.
| Code: |
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,121,
TRAILER3=(122:C'TOTAL : ',TOT=(122,6,ZD),/),
131,7,
TRAILER3=(1,121,
TOT=(122,7,ZD),
1,121)) |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
You need two "totals" for the tasks. One is when the first three columns change (from memory) and here you want just a total followed by a blank line.
The second is that you want a summary value for the first 10 columns and you want that total to be "embedded" in the line with the 10 columns and the to-be-done-yet Column 12.
| Code: |
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,121,
TRAILER3=(122:C'TOTAL : ',TOT=(122,6,ZD),/),
131,7) |
The above code does neither.
Try something like
| Code: |
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,length-of-first-three-columns-on-output,
TRAILER3=(122:C'TOTAL : ',TOT=(122,6,ZD),/))
|
That, on its own, should get you close to the totals you want for the first three columns. You can adjust the output for how you want it to look.
| Code: |
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,121,
TRAILER3=(1,121,TOT=(122,6,ZD),128,10))
|
That, or close to it, on its own should give the summary line for the details.
They can be combined, something like this:
| Code: |
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,length-of-first-three-columns-on-output,
TRAILER3=(122:C'TOTAL : ',TOT=(122,6,ZD),/),
1,121,
TRAILER3=(1,121,TOT=(122,6,ZD),128,length of remainder of line))
|
Remember you want the 6,ZD field to be "longer" to allow for the TOT. That plus the "consolidation" of COL 12 will be next, once you get the OUTFIL working and show all the Control Cards you have so far.
Again, I have not run the above myself, so you fix the bits that go wrong, rather than complain. |
|
| Back to top |
|
|
venkata.ravi
New User
Joined: 09 May 2005
Posts: 30
Location: Hyderabad
|
|
|
|
Hi Bill,
The below code is summing up totals
if identical records (COL1 to COL10 (1,121) and COL12 (128,50) but with different COL11's
| Code: |
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,121,128,50,
TRAILER3=(1,121,TOT=(122,6,ZD),128,50)) |
With this sort card, the output file becomes 192 bytes and COL12 starts from 138 |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Are you saying this is some type of problem? If yes, be clear about it. Look at the size of the thread. This should not take so much effort.
You didn't previously mention you wanted to "total" if COL1-10 were equal AND COL12 was equal. You specified completely different processing for COL12.
Is this a change?
When I wrote my code, I used an EDIT for the TOT there. Have you tried that?
Remember, last thing you said was you wanted the (up to) 10 values for COL12 consolidated on the one line, which will be an extension of the line by 450 bytes.
Also, again, remember that you want an extra position for COL11, so that the total is less likely to overflow. The start position of COL12 (consolidated or otherwise) will have to change by one to be able to do that. |
|
| Back to top |
|
|
venkata.ravi
New User
Joined: 09 May 2005
Posts: 30
Location: Hyderabad
|
|
|
|
Hi Bill,
It's not the new issue at all.
COL11 should be summed up if COL1 to COL10 are equal, Before computing the totals, we need to pair up all the different COL12 values. With the above sort card, Totals can be computed if COL1 to COL10 and COL12 values are equal.
| Code: |
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,121,128,50,
TRAILER3=(1,121,TOT=(122,6,ZD),128,50)) |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Code: |
COL1-10 somevalue
COL11 amount1
COL12 A
COL1-10 samevalueasabove
COL11 amount2
COL12 B
COL1-10 samevalueasabove
COL11 amount3
COL12 A
|
With your code the above will get three output records with no "summing", ie your output will be equal to your input..
What you said you wanted, it seems to me, is one "summed" record of COL1-20 with A,B in COL12.
So, what do you want in the above case? |
|
| Back to top |
|
|
venkata.ravi
New User
Joined: 09 May 2005
Posts: 30
Location: Hyderabad
|
|
|
|
Hi Bill,
with my sort card, and with your criteria, it will result in only 2 records. which is expected.
| Code: |
Input taken
--+----2----+----3---
*********************
20130306000150ABCDE
20130306000250B
20130306000350ABCDE
*********************
Output:
--+----2----+----3----+----4--
******************************
20130306 500 ABCDE
20130306 250 B
****************************** |
COL11 converts to ZD. 150+350 summed up and becomes 500 for COL12 ABCDE, as they are same. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Presumably you are sorting on COL12 then, which you didn't mention.
Presumably also you no longer need the "consolidation" of COL12?
That's good.
So you just need to extend the COL11 for the TOT so it doesn't overflow so easily. I'd append C'0',startCOL11,lengthCOL11 to the end of the record and TOT on that. |
|
| Back to top |
|
|
venkata.ravi
New User
Joined: 09 May 2005
Posts: 30
Location: Hyderabad
|
|
|
|
Hi Bill,
I'm still working on consolidation of COL12 values, which is the last step in the requirement.
On the other hand, I have just sent the sample reports to users and requested their feedback on the output file (this does not cover consolidation of COL12). I'm waiting for their response on this. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
