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